Re: Automatic date/time

On Mar 13, 2006, at 22:37 , John Christopher wrote:

> To follow up on that, how to make sure the timestamp is always stored

Timestamps are stored as an absolute value, i.e., independent of time
zone offset. The time zone that is shown is the time zone of the
server. This is not something you define in DDL. (There has been
discussion of providing a method of actually saving the time zone
information as well, but it hasn't been implemented yet.) You can use
SET to change the time zone during the session:

http://www.postgresql.org/docs/current/interactive/sql-set.html

For example:
test=# select current_timestamp;
              now
------------------------------
2006-03-13 23:23:04.09736+09
(1 row)

test=# set time zone 'PST8PDT';
SET
test=# select current_timestamp;
               now
-------------------------------
2006-03-13 06:23:27.408327-08
(1 row)

or use the AT TIME ZONE construct, depending on what you want to do.

http://www.postgresql.org/docs/current/interactive/functions-
datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

test=# select current_timestamp at time zone 'UTC';
           timezone
----------------------------
2006-03-13 14:24:14.901873
(1 row)


Michael Glaesemann
grzm myrealbox com