When “timestamp without time zone” beats “timestamp with time zone”

I’m the kind of guy that keeps a list of all the movies he’s watched, with dates. Additionally, I’m the kind of guy that used to keep such list in an SQLite database, and that keeps it in a PostgreSQL database now. Heh.

I used to register just the day in which I watched a certain film. When moving to Postgres, I decided to record the time of day as well, which can come handy for those times where I’d watch 2 films in the same day, in order to know which one came first. (I seem to have a small number of dates registered where I watched 3 films on the same day.)

So, I went for a “timestamp with time zone” column. As its name seemed to promise, I was expecting for it to store the absolute timestamp of the event, plus the time zone it occurred in. But it doesn’t work that way: a timestamp with time zone column only stores the absolute timestamp of the event, but not the associated time zone information. When returning the data to you, the event will always be in the local time zone, meaning that the only use for the “with time zone” part is that you can feed PostgreSQL dates in different time zones, and it will know to convert them all to absolute UTC before storing them in the database.

This was a tad upsetting, because it meant I was actually losing information. For me, the most relevant information to store was the date of the event (as in, which day), and I was only storing the time for the purposes stated above. Should I move to a different timezone, either permanently or temporarily, some or all of the dates could very well be off by one day.

The proper solution if you’re such a pedant is to store in an additional colum the time zone, either as a symbolic name, or as a numeric offset. But I don’t care that much, so I’ve gone for a “timestamp without time zone” column, which solves the problem just fine: give me the date this event happened, in whatever time zone you happened to be at the time.

By the way, one of my main objectives with keeping my list of watched movies in a database was to be able to quickly answer the question: What other films have I watched this actor in? Is a question that I make myself surprisingly often (because I’ll know I’ve seen a face before, but will not remember where), and an SQL statement is oh-so-much-more convenient than visiting an IMDB page and scan it for titles you think you’ve watched.