When to use a timestamp without time zone?
There is a long and quite elucidating answer on the differences between
TIMESTAMP WITH TIME ZONE -vs-
TIMESTAMP WITHOUT TIME ZONE
available in the SO post Ignoring time zones altogether in Rails and PostgreSQL.
What I would like to know is: Are there any valid use cases for actually using TIMESTAMP WITHOUT TIME ZONE or should it be considered an anti-pattern?
This is stated in a lot of places, but I think it is worth mentioning always when we compare the timestamp with time zone with timestamp without time zone types: the timestamp WITH time zone does not store the time zone information along with the timestamp. What it does is to store every data in UTC time zone, as stated in the docs:
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's Time Zone parameter, and is converted to UTC using the offset for the time zone .
It is considered valid for some to use a timestamp WITHOUT time zone in situations where (1) everything is in the same time zone or (2) the application layer handles the time zone and just stores everything in a certain time zone (usually UTC). But it is also considered an anti-pattern, simple because the correct solution for (1) is to configure the Time Zone setting to the given one time zone for the system and (2) is already solved, as PostgreSQL already stores everything on the same timezone (UTC).
Now, with those two down, I can come up with only one good reason to use timestamp WITHOUT time zone. That is when you want to store events in the future and that some kind of alert must be triggered when we get to that time. That could be good for timestamp WITH time zone if, and only if, the rules defined by the region's laws about time zone didn't ever change. The most common rule that changes is about the adoption or not of daylight saving time (DST).
For example, imagine that you are at, let's say, 2013-06-15 (not yet in DST) schedule some event to happen at 2013-01-15 10:00 (which would be already in DST), and at 2013-06-15 your region was designated to adopt DST; but, some time after that, the government changed the rule and say your region will no longer use DST, and suddenly your scheduled time becomes 2013-01-15 11:00 (instead of 10:00), that if you use timestamp WITH time zone, and keep your TZ configurations up-to-date. Of course you may also notice that it is possible to treat such cases also with time zone, if you keep track of the rule changes in the regions/time zones of your interest, and update the affected records. Worth mentioning that some regions do often change these rules (like in Brazil, some states - not the entire country - often change), but in most cases it changes very earlier, so your users would be affected only by events scheduled very far from the current time. With all that said, I only have one more suggestion. If you do have users, logs, or anything on different time zones, store the time zone they are coming from somewhere and choose and use a timestamp with time zone. That way you can (1) cross events happening closer to each other for different sources (independent of their timezones) and (2) show the original time (the clock time) the event has happened.