Re: timestamp with time zone - Mailing list pgsql-novice

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