Re: timestamp with time zone

On 02/07/2012 10:30 AM, Alessandro Gagliardi wrote:
> I'm trying to insert a timestamp with a time zone, the time zone is
> automatically set to the time zone of the server, not the time zone I
> specify in my statement. For example, if I try:
> INSERT INTO blocks ("moment_type", "user_id", "block_id", "created")
> VALUES
> ('thought','4d0b983456a35863fe000bec','4f31670df1f70e6cc2000ac3','2012-02-07T21:01:49.329590+03:00'::timestamptz);
> and then:
> SELECT created FROM blocks WHERE block_id = '4f31670df1f70e6cc2000ac3';
> I get "2012-02-07 10:01:49.32959-08". Incidentally 2012-02-07
> 10:01:49.32959-08 is the same time
> as 2012-02-07T21:01:49.329590+03:00 so it's not like it's ignoring the
> time zone information. But I need to be able to query based on the
> local time of the user, not the server, so
> resolving 2012-02-07T21:01:49.329590+03:00 as 2012-02-07
> 10:01:49.32959-08 is not useful to me.
> I feel like there must be something stupid I'm doing wrong. Of course
> I could store the time zone separately and then make the adjustment on
> the other end, but that would seem to defeat the purpose of having a
> TIMESTAMP WITH TIME ZONE data type.
>
>
Timestamp with time zone is IMHO a bad name for this data type. It is,
in fact, a "point in time". So 2012-02-07T21:01:49.329590+03:00 and
2012-02-07 10:01:49.32959-08 are just different representations of the
same point in time. How PostgreSQL stores it internally is not relevant.
What is important is that you can display that point in time in the
format and at the time zone you choose.

You have a couple alternatives. One is to use the "set timezone to"
statement prior to your "select" statement. I recommend using the full
name for the time zone, i.e. posix/Asia/Macao rather than CST since CST
could also be Central Standard Time or a variety of other zones
depending on how the server is set to interpret abbreviations. Note,
also, that using the zone name implies that it will change the offset
according to daylight saving rules. If you specify a zone by an offset
like -8 you will just get that offset from GMT without regard for any
DST rules.

If you set your timezone then select a timestamp with time zone you will
get a timestamp with time zone shown as the offset appropriate to that
point in time in your selected zone and according to DST rules.

The other is to use select sometimestamptz at time zone 'timezonename'.

This will return the timestamptz (point in time) as a timestamp
*without* timezone but adjusted according to the rules for the specified
timezone.

Examples:

steve=# select * from pg_timezone_names limit 10;
name | abbrev | utc_offset | is_dst
----------------------+--------+------------+--------
Portugal | WET | 00:00:00 | f
Arctic/Longyearbyen | CET | 01:00:00 | f
GMT-0 | GMT | 00:00:00 | f
posixrules | EST | -05:00:00 | f
Antarctica/Palmer | CLST | -03:00:00 | t
Antarctica/Macquarie | MIST | 11:00:00 | f
Antarctica/Casey | WST | 08:00:00 | f
Antarctica/McMurdo | NZDT | 13:00:00 | t
Antarctica/Vostok | VOST | 06:00:00 | f
Antarctica/Mawson | MAWT | 05:00:00 | f
(10 rows)

steve=# select now(); -- I'm in Pacific time
now
-------------------------------
2012-02-07 10:52:19.212832-08
(1 row)

steve=# set timezone to 'posix/Asia/Macao';
SET
steve=# select now();
now
------------------------------
2012-02-08 02:52:52.37288+08
(1 row)

steve=# select now() at time zone 'Africa/Djibouti';
timezone
----------------------------
2012-02-07 21:53:58.842838
(1 row)

steve=# set timezone to DEFAULT ;
SET
steve=#

Cheers,
Steve