Re: timestamp with time zone

On 02/07/2012 11:12 AM, 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.
>
Your choice depends on what you want to do. If the data is only relevant
to as it relates to a person in that person's time zone, then timestamp
without tz is enough. Perhaps only time is needed. ("Breakfast is at 7am").

If you need events coordinated across many timezones ("the rocket is
scheduled to launch at...") then you need a timestamp with time zone to
represent that point in time.

You can also store a reference timezone. If it is relevant to people or
users, it could be stored in that table. If it is related to the time
zone where an event occurs, it could be stored in the events table.
Either way, you can display or sort based on the point-in-time or the
local time:

Table "public.events"
Column | Type | Modifiers
------------+--------------------------+-----------
event | text |
event_time | timestamp with time zone |
event_zone | text |

steve=# select event, event_time, event_zone, event_time at time zone
event_zone from events;
-[ RECORD 1 ]-----------------------------
event | breakfast
event_time | 2012-02-07 11:50:36.002843-08
event_zone | Africa/Djibouti
timezone | 2012-02-07 22:50:36.002843
-[ RECORD 2 ]-----------------------------
event | breakfast
event_time | 2012-02-07 11:23:10.702886-08
event_zone | America/Chicago
timezone | 2012-02-07 13:23:10.702886
-[ RECORD 3 ]-----------------------------
event | lunch
event_time | 2012-02-07 10:13:49.432886-08
event_zone | US/Indiana-Starke
timezone | 2012-02-07 12:13:49.432886

Cheers,
Steve