By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,708 Members | 2,080 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,708 IT Pros & Developers. It's quick & easy.

timestamp with time zone question...

P: n/a
i have a table with a 'timstamp with time zone' column.

when i insert into it:

'18 Nov 2004 00:00:00 PST'

....it looks like:

2004-11-18 03:00:00-05

....once stored in the database.
0 o'clock vs. 3 o'clock.

is it storing in the time zone of the actual server or something? not
sure where my db server actually, physically is.

if so, does that imply that i can translate these stored dates to any
target time zone of my choosing when selecting the date from the db?
- philip
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Sun, Nov 21, 2004 at 07:58:37PM -0600, P. George wrote:
is it storing in the time zone of the actual server or something? not
sure where my db server actually, physically is.
See the documentation for Date/Time Types and Date/Time Functions
and Operators:

http://www.postgresql.org/docs/7.4/s...-datetime.html
http://www.postgresql.org/docs/7.4/s...-datetime.html
if so, does that imply that i can translate these stored dates to any
target time zone of my choosing when selecting the date from the db?


Yes -- see the aforementioned documentation.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2

P: n/a
> See the documentation for Date/Time Types and Date/Time Functions
and Operators:

http://www.postgresql.org/docs/7.4/s...-datetime.html
http://www.postgresql.org/docs/7.4/s...-datetime.html
if so, does that imply that i can translate these stored dates to any
target time zone of my choosing when selecting the date from the db?


Yes -- see the aforementioned documentation.

well... the examples in the documentation work, but they only use
static dates (hard-coded, i mean):

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
'PST';

but, when i try:

SELECT mydatecolumn FROM mytable AT TIME ZONE 'PST';

.... i get a parse error.

same thing with:

SELECT TIMESTAMP WITH TIME ZONE mydatecolumn FROM mytable AT TIME ZONE
'PST';

any ideas?

thanks.

- philip

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3

P: n/a

On Nov 22, 2004, at 11:30 AM, P. George wrote:
well... the examples in the documentation work, but they only use
static dates (hard-coded, i mean):

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
'PST';

but, when i try:

SELECT mydatecolumn FROM mytable AT TIME ZONE 'PST';

.... i get a parse error.


It would help if you included the exact error. However, one thing the
examples in the docs show, is that the AT TIME ZONE follows the value,
i.e.,

SELECT mydatecolumn AT TIME ZONE 'PST' FROM mytable;

hth

Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4

P: n/a
oops. that did it. ;-)

thanks.

- philip

On Nov 21, 2004, at 8:42 PM, Michael Glaesemann wrote:

On Nov 22, 2004, at 11:30 AM, P. George wrote:
well... the examples in the documentation work, but they only use
static dates (hard-coded, i mean):

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
'PST';

but, when i try:

SELECT mydatecolumn FROM mytable AT TIME ZONE 'PST';

.... i get a parse error.


It would help if you included the exact error. However, one thing the
examples in the docs show, is that the AT TIME ZONE follows the value,
i.e.,

SELECT mydatecolumn AT TIME ZONE 'PST' FROM mytable;

hth

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.