Re: timestamp with time zone

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