Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a long and quite elucidating answer on the differences between

  • TIMESTAMP WITH TIMEZONE -vs-
  • TIMESTAMP WITHOUT TIMEZONE

available in this SO post. What I would like to know is: are there any valid use cases for actually using TIMESTAMP WITHOUT TIMEZONE or should it be considered an anti-pattern.

share|improve this question
    
This question runs the risk of being closed being "primarily opinion based". I've tried to give some objective views in my answer below. –  Colin 't Hart Feb 16 at 15:24
add comment

2 Answers 2

up vote 2 down vote accepted

This is stated in a lot of places, but I think it 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 TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

It is considered valid for some to use timestamp WITHOUT time zone in situations where (1) everything is in the same timezone or (2) the application layer handles the time zone and just store 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 TimeZone setting to the given one timezone 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 came 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 got to that time. That could be good for timestamp WITH time zone if, and only if, the rules defined by region's laws about time zone didn't ever change. The most common rule that changes is about the adoption or not of day light 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 you region was designated to adopt DST, but, some time after that, the govern change 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/timezones of your interest, and update the affected records.

Worth mentioning that some regions does often change these rules (like at Brazil, some states - not the entire country - often change), but in most cases it changes it 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 timezones, store the timezone they are coming from somewhere and choose and use 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.

share|improve this answer
    
You are saying: "I can came with only one good reason to use timestamp with time zone". If that's not a typo then are you indeed suggesting that "timestamp without time zone" is actually more appropriate / less prone to produce misunderstandings than "timestamp with time zone"? That seems counter-intuitive to me. –  Marcus Junius Brutus Feb 17 at 12:24
    
@MarcusJuniusBrutus, sorry about that, it was indeed a typo and I thought the other way around... Check the edited answer. –  MatheusOl Feb 17 at 12:50
add comment

The date timestamp also doesn't include timezone information, yet a lot of people use it.

Of course, that of itself is not an answer.

It's valid to use timestamp and date for any timestamps and dates which are always in the same timezone, or are being stored relative to some known timezone.

If the timezone is always the same or implicit then it's more of antipattern to store it than to not store it (redundant information).

Timestamps can also be used for storing technical information such as used in application logs or for performance measurements. In this case, the timezone can also be unimportant.


Conversely, if you are designing or working on a distributed system or in any system where parts of the system will be distributed across different timezones, it might be considered to be an anti-pattern to not use timestamp with timezone, although some systems might be designed to run in one timezone, eg UTC. In this case the timezone logic may be being pushed into the application layer -- but I would consider this an anti-pattern, yes.

share|improve this answer
    
I understand that in certain cases it doesn't hurt to use timestamp without timezone, but does it ever buy me anything? Otherwise, why should I bother if the timestamp with timezone data type is always equally or more appropriate? –  Marcus Junius Brutus Feb 16 at 15:35
    
Not storing redundant information would be my main argument. I presume date arithmetic on timestamp without timezone would be faster, too, but that is probably only important if you are processing billions of rows... –  Colin 't Hart Feb 16 at 15:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.