Thread: timezone help?

I'm a bit new to PG, and having troubles with timestamps.  The docs list:

timestamp [ (p) ] [ without time zone ]    8 bytes    both date and time    4713 BC    5874897 AD    1 microsecond / 14
digits
timestamp [ (p) ] with time zone    8 bytes    both date and time, with time zone    4713 BC    5874897 AD    1
microsecond/ 14 digits 

But an example of how to call to_timestamp either with or without a TZ would help.



My immediate problem is below..

create or replace function get_thermom_type(siteid integer, observationtime timestamp)

select get_thermom_type(1,to_timestamp('01-JAN-2011','DD-MON-YYYY') );
ERROR:  function get_thermom_type(integer, timestamp with time zone) does not exist
LINE 1: select get_thermom_type(1,to_timestamp('01-JAN-2011','DD-MON...
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


How do I tell to_timestamp to forget the about time zones?  this didn't work either:

get_thermom_type(1,to_timestamp('01-JAN-2011 HH24:MI','DD-MON-YYYY 13:01') without time zone);
nor a bunch of other attempts.


Thanks for any help,

-Dave

On Tuesday, July 19, 2011 12:01:19 pm David Salisbury wrote:
> I'm a bit new to PG, and having troubles with timestamps.  The docs list:
>
> timestamp [ (p) ] [ without time zone ]    8 bytes    both date and time    4713
> BC    5874897 AD    1 microsecond / 14 digits timestamp [ (p) ] with time zone
8
> bytes    both date and time, with time zone    4713 BC    5874897 AD    1 microsecond
> / 14 digits
>
> But an example of how to call to_timestamp either with or without a TZ
> would help.
>
>
>
> My immediate problem is below..
>
> create or replace function get_thermom_type(siteid integer, observationtime
> timestamp)
>
> select get_thermom_type(1,to_timestamp('01-JAN-2011','DD-MON-YYYY') );
> ERROR:  function get_thermom_type(integer, timestamp with time zone) does
> not exist LINE 1: select
> get_thermom_type(1,to_timestamp('01-JAN-2011','DD-MON... ^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
> How do I tell to_timestamp to forget the about time zones?  this didn't
> work either:
>
> get_thermom_type(1,to_timestamp('01-JAN-2011 HH24:MI','DD-MON-YYYY 13:01')
> without time zone); nor a bunch of other attempts.

How about:
test(5432)aklaver=>SELECT to_timestamp('01-JAN-2011','DD-MON-YYYY')::timestamp;
    to_timestamp
---------------------
 2011-01-01 00:00:00


>
>
> Thanks for any help,
>
> -Dave

--
Adrian Klaver
adrian.klaver@gmail.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Jul 19, 2011 at 12:15:54PM -0700, Adrian Klaver wrote:
> On Tuesday, July 19, 2011 12:01:19 pm David Salisbury wrote:
> > I'm a bit new to PG, and having troubles with timestamps.  The docs list:

[...]

> > My immediate problem is below..
> >
> > create or replace function get_thermom_type(siteid integer, observationtime
> > timestamp)

[...]

> How about:
> test(5432)aklaver=>SELECT to_timestamp('01-JAN-2011','DD-MON-YYYY')::timestamp;
>     to_timestamp
> ---------------------
>  2011-01-01 00:00:00

Simply TIMESTAMP '01-JAN-2011' should work too:

 | test=# create or replace function fu(t timestamp) returns timestamp as
 | $$
 | begin
 |   return $1;
 | end $$ language plpgsql;
 | CREATE FUNCTION
 | test=# select fu(TIMESTAMP '01-JAN-2011');
 |          fu
 | ---------------------
 |  2011-01-01 00:00:00
 | (1 row)

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFOJeNvBcgs9XrR2kYRAm9yAJ9EgZ3IN9Y1WrsQ9ke4rNXVGLEF6gCeI8/L
Ii66It4LvdL/6reVNti9ZBo=
=8fUi
-----END PGP SIGNATURE-----

On 07/19/2011 12:01 PM, David Salisbury wrote:
>
> I'm a bit new to PG, and having troubles with timestamps.  The docs list:
>
> timestamp [ (p) ] [ without time zone ]    8 bytes    both date and
> time    4713 BC    5874897 AD    1 microsecond / 14 digits
> timestamp [ (p) ] with time zone    8 bytes    both date and time,
> with time zone    4713 BC    5874897 AD    1 microsecond / 14 digits
>
> But an example of how to call to_timestamp either with or without a TZ
> would help.
>
>
>
> My immediate problem is below..
>
> create or replace function get_thermom_type(siteid integer,
> observationtime timestamp)
>
> select get_thermom_type(1,to_timestamp('01-JAN-2011','DD-MON-YYYY') );
> ERROR:  function get_thermom_type(integer, timestamp with time zone)
> does not exist
> LINE 1: select get_thermom_type(1,to_timestamp('01-JAN-2011','DD-MON...
>                ^
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
>
>
> How do I tell to_timestamp to forget the about time zones?  this
> didn't work either:
>
> get_thermom_type(1,to_timestamp('01-JAN-2011 HH24:MI','DD-MON-YYYY
> 13:01') without time zone);
> nor a bunch of other attempts.

As an aside to the other comments, if you are logging data observations
I would use timestamp with time zone (timestamptz). Despite the name, a
timestamp with time zone does not contain any time zone information and
is better referred to as a "point in time". You can input or output that
"point in time" as UTC, Antarctica/Vostok, America/Phoenix or whatever
location is appropriate for your situation and can change the display of
your stored points-in-time as you desire. But whether displayed as
2011-07-19 12:00 PDT or 2011-07-19 15:00 EDT the data represents a
distinct point in time.

If you use a basic timestamp without time zone and daylight saving time
is in any way involved you will have ambiguity when the clock falls back
and you have no way to accurately determine when 2011-11-06 01:30 really
was. With a timestamp with time zone you can tell, for example, that it
was 2011-11-06 01:30 PDT or 2011-11-06 01:30 PST.

Cheers,
Steve