Обсуждение: timestamp with time zone question...

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

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/static/datatype-datetime.html
http://www.postgresql.org/docs/7.4/static/functions-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/

> See the documentation for Date/Time Types and Date/Time Functions
> and Operators:
>
> http://www.postgresql.org/docs/7.4/static/datatype-datetime.html
> http://www.postgresql.org/docs/7.4/static/functions-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

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

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
>