Re: timestamp with time zone

Makes sense. I think I'm going to go ahead and agree with Steve and say
that "timestamp with time zone" is a bad name for this datatype but at
least I understand it now. I think I'll go with your second solution since
it only requires the addition of one column.

Thank you both.
-Alessandro

On Tue, Feb 7, 2012 at 11:42 AM, Philip Couling <phil(at)pedal(dot)me(dot)uk> wrote:

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