Thread: 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

Hi

Designing your data model comes down to what you want to do and how you
most commonly view the data.

You originally tried to store this as a timestamp with a time zone.  You
can of course do just that.  Two fields, one a time stamp and the other
a timezone (stored as a varchar).

You can store the timezone against the user or event depending on what's
appropriate for you.  The choice is still yours whether to store the
timestamp as a local time or an absolute point in time.

If you use a timestamp without time zone (local time stamp) then you can:
SELECT event_time FROM events; -- to get the local time
SELECT event_time AT time zone event_timezone FROM events; -- to get the
absolute time

If you use a timestamp with time zone (absolute point in time) then you can
SELECT event_time FROM evets; -- to get the absolute time
SELECT event_time AT time zone event_timezone FROM events; -- to get the
local time

This may seem more elegant that setting the session time zone before
each statement (it does to me).

Regards



SELECT extract(hour from created) AS created_hour, COUNT(block_id) FROM
blocks GROUP BY created_hour ORDER BY created_hour


On 07/02/12 19:12, Alessandro Gagliardi wrote:
> Hm. This seems rather inelegant if I'm going to be resetting the
> timezone in a separate statement every time I want to insert a row.
>
> Say I want to find out what time of day people tend to create blocks
> in their own local time (i.e. SELECT extract(hour from created) AS
> created_hour, COUNT(block_id) FROM blocks GROUP BY created_hour
> ORDER BY created_hour).
>
> I'm thinking maybe the solution is to actually add a separate
> TIMESTAMP WITHOUT TIME ZONE column that strips out the timezone
> information and stores the timestamp in the local time.
>
> On Tue, Feb 7, 2012 at 10:55 AM, Steve Crawford
> <scrawford@pinpointresearch.com
> <mailto:scrawford@pinpointresearch.com>> wrote:
>
>     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
>
>

On 02/07/2012 11:12 AM, Alessandro Gagliardi wrote:
> Hm. This seems rather inelegant if I'm going to be resetting the
> timezone in a separate statement every time I want to insert a row.
>
> Say I want to find out what time of day people tend to create blocks
> in their own local time (i.e. SELECT extract(hour from created) AS
> created_hour, COUNT(block_id) FROM blocks GROUP BY created_hour
> ORDER BY created_hour).
>
> I'm thinking maybe the solution is to actually add a separate
> TIMESTAMP WITHOUT TIME ZONE column that strips out the timezone
> information and stores the timestamp in the local time.
>
Your choice depends on what you want to do. If the data is only relevant
to as it relates to a person in that person's time zone, then timestamp
without tz is enough. Perhaps only time is needed. ("Breakfast is at 7am").

If you need events coordinated across many timezones ("the rocket is
scheduled to launch at...") then you need a timestamp with time zone to
represent that point in time.

You can also store a reference timezone. If it is relevant to people or
users, it could be stored in that table. If it is related to the time
zone where an event occurs, it could be stored in the events table.
Either way, you can display or sort based on the point-in-time or the
local time:

                Table "public.events"
    Column   |           Type           | Modifiers
------------+--------------------------+-----------
  event      | text                     |
  event_time | timestamp with time zone |
  event_zone | text                     |


steve=# select event, event_time, event_zone, event_time at time zone
event_zone from events;
-[ RECORD 1 ]-----------------------------
event      | breakfast
event_time | 2012-02-07 11:50:36.002843-08
event_zone | Africa/Djibouti
timezone   | 2012-02-07 22:50:36.002843
-[ RECORD 2 ]-----------------------------
event      | breakfast
event_time | 2012-02-07 11:23:10.702886-08
event_zone | America/Chicago
timezone   | 2012-02-07 13:23:10.702886
-[ RECORD 3 ]-----------------------------
event      | lunch
event_time | 2012-02-07 10:13:49.432886-08
event_zone | US/Indiana-Starke
timezone   | 2012-02-07 12:13:49.432886

Cheers,
Steve