Re: timestamp with time zone

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