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(at)pinpointresearch(dot)com
> <mailto:scrawford(at)pinpointresearch(dot)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
>
>