Re: timestamp timezone problem

On Aug 1, 2005, at 1:29 PM, <operationsengineer1(at)yahoo(dot)com>
<operationsengineer1(at)yahoo(dot)com> wrote:

> select now()::timestamptz
>
> in pgadmin3's sql window, it ends with -07. as i
> understand it, that is the time zone. my computer is
> GMT-8 since i'm in the pacific time zone.
>
> why is there a discrepancy - or is it just me not
> knowing the nuts and bolts of what is going on?

Daylight savings time:

show timezone;
TimeZone
----------
EST5EDT
(1 row)

EDT = Eastern Daylight Time, otherwise it would be EST = Eastern
Standard Time.

>
> to tz or not tz? that is the question. i think i want
> to tz b/c i can't guarantee that tz won't become
> relevant in the future (ie, data may be entered from
> two different time zones a report may want to compare
> the dates).

This is exactly the reason to include the time zone -- if it will be
important to compare time generated from more than one time zone. So
the need for this depends on your application.

> if i insert now() into a timestamp field, will it
> insert the local time or the gmt time? i think it is
> the local time, but i want to be sure before i set up
> my table.

Local time. Try it yourself with

select now()::timestamp;
now
----------------------------
2005-08-01 18:55:21.211502
(1 row)

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL