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