Skip site navigation (1) Skip section navigation (2)

Re: Automatic date/time

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: "John Christopher" <jcxxr(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Automatic date/time
Date: 2006-03-13 14:27:28
Message-ID: D9B1C736-DC8B-4DE0-BDA9-7741348497F8@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
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




In response to

pgsql-novice by date

Next:From: Jonathan DaviesDate: 2006-03-13 16:51:07
Subject: Return count between timestamps
Previous:From: John ChristopherDate: 2006-03-13 13:37:16
Subject: Re: Automatic date/time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group