'timestamp with timezone' not supported about pg

[vmihailenco]

I will add support for timestamp with timezone data type, but I strongly recommend you to use timestamp without timezone.

[johto]

I don't mean to barge in with metaphorical guns blazing, but in almost every case using timestamp without time zone is a mistake. The behaviour of Go's time.Time is also closer to timestamp with time zone.

[vmihailenco]

Your experience is undeniable, but can you please explain why? I can't think of the situation where timestamp with time zone is useful... timestamp without timezone + separate field to store user time zone looks like a clear winner for me in all cases I can imagine...

[johto]

timestamp without timezone + separate field to store user time zone looks like a clear winner for me in all cases I can imagine...

The most common use for timestamps in the database (in my experience, at least) is recording the absolute point in time when something happened. If you truly meant to say using timestamp and the user's time zone (i.e. the time zone in which the event happened), there's no way to tell when exactly it happened during the repeated hour at the DST boundary. E.g.:

SELECT '2014-03-30 02:00:00'::timestamp AT TIME ZONE 'Europe/Stockholm';
        timezone        
------------------------
 2014-03-30 03:00:00+02
(1 row)

Of course, you can get around that by storing the offset as well, but now you have three fields to worry about and the math gets very hairy. Even ignoring that issue, querying gets more difficult. Say you want all events that happened within the last five minutes:

timestamptz:
    SELECT * FROM events WHERE time >= now() - interval '5 minutes'
timestamp + user's location:
    SELECT * FROM events WHERE time AT TIME ZONE tz >= now() - interval '5 minutes'
timestamp in UTC:
    SELECT * FROM events WHERE time >= (now() - interval '5 minutes') AT TIME ZONE 'Etc/UTC'

The first one is clearly the easiest. Storing the time in timestamp+user's time zone is the most difficult, and it requires an extra index. The other two can work with just an index on "time" alone.

[vmihailenco]

Tests can be found here: https://github.com/vmihailenco/pg/blob/master/main_test.go#L247