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