Timestamps and Time Zones in PostgreSQL (2016)

I think I slightly disagree about the choice of using timestamptz over timestamp. The main reason being that when I feed data in to the database I always want that same data back out. From the description I now realize that if I backed up and restored, or otherwise ran the same database with a different configuration the database might "try to be smart" and return a modified value.

I guess to store a timestamp and a timezone I should use a timestamp a second column with the timezone value (maybe a string, but I'd like a clean way of telling the database I want an 'enum' but to dynamically just add new values as it sees them; yes for PostgreSQL I'd do that with a secondary lookup table and an insert/update trigger).

It would return an equivalent value, which is the important thing. You can specify `AT TIME ZONE 'UTC'` (or whichever timezone) when consuming it to get a consistent look. On the other hand, it’s not clear from just the value what point in time a `timestamp without time zone` represents. That’s why I agree that it should be used when you don’t want to represent a point in time.

Moreover, `AT TIME ZONE` only works correctly when used following the semantics described in the article. It will do the opposite of what you think it should if you use `timestamp without time zone` to represent an absolute time, or vice-versa.

I guess you're right.

I have a side project that stores radios' schedules and started with timestamptz (while storing in UTC anyway) and thought I did the right thing.

Then I converted part of the project to Elixir and discovered that Ecto use timestamp by default. I thought it was odd but looked into the reasoning and discovered the misnaming of the "with timezone" part and that José Valim was right as usual ;)

What I ended up using in my select queries is something along the lines of :

> s.date_time_start at time zone 'UTC' at time zone 'Europe/Paris' as start_at

Unrelated but at work Friday (while reading this article, as fate would have it) I had to debug a erroneous time displayed to some users by text. I found one line in the service that forgot to specify the timezone while formatting the datetime. It used to work so what changed ? Well our users are increasingly international and we recently move those cron jobs from one server in Ireland to multiple AWS zones so what seemed to work for years before showed up now as a bug. I also know they had a hectic debug day six months ago during the last DST event. In conclusion, dates are hard.