Yes. There are use-cases for
TIMESTAMP WITHOUT TIME ZONE.
- In common business apps this type would only be used for:
- Booking future appointments
- Representing the same time-of-day across various time zones, such as noon on the 23rd in Tokyo and in Paris (two different moments hours apart, same time-of-day)
- For tracking moments, specific points on the timeline, always use
TIMESTAMP WITH TIME ZONE, not
TIMESTAMP WITHOUT TIME ZONE values are not a point on the timeline, not actual moments. They represent a rough idea about potential moments, possible points on the timeline along a range of about 26-27 hours (the range of time zones around the globe). They have no real meaning until you apply a time zone or offset-from-UTC.
For example, say you need to record the start of holidays/holy days.
Column: year_ Type: SMALLINT
Column: description_ Type: VARCHAR
Column: start_ Type: TIMESTAMP WITHOUT TIME ZONE
To record the fact that Christmas starts after midnight on December 25 this year, we need to say
2016-12-25 00:00:00 without any time zone. Early in Santa’s day he visits Auckland NZ just after midnight, as that is one of the earliest midnights on the globe. Then he works his way westward, as the next midnight happens, soon reaching the Philippines. Then the reindeer move on in a westerly direction, reaching India at its midnight which happens to occur several hours after that Auckland midnight. Much later still is midnight in Paris FR, and still later midnight in Montreal CA. All these visits by Santa happen at different moments is time, yet all happened shortly after midnight, per each locality’s own midnight.
2016-12-25 00:00:00 without any time zone as the beginning of Christmas is informative and legitimate, but only vaguely. Until you say “Christmas in Auckland” or “Christmas in Montréal”, we do not have a specific moment in time. If you are recording the actual moment each time the sleigh touched down, you would use
TIMESTAMP WITH TIME ZONE rather than the
Similar to Christmas is New Year’s Eve. When the Times Square Ball drops in New York, people in Seattle are still chilling their champagne and preparing their party horns. Yet we would record the idea of the New Year moment as
2017-01-01 00:00:00 in a
TIMESTAMP WITHOUT TIME ZONE. In contrast, if we want to record when the ball dropped in New York, or when the people in Seattle blew their horns, we would instead use
TIMESTAMP WITH TIME ZONE (not
WITHOUT) to record those actual moments, each three hours apart from the other.
Ex: Factory Shifts
Another example might be recording a policy that involves wall-clock time across various locations. Say we have factories in Detroit, Düsseldorf, and Delhi. If we say that at all three factories the first shift begins at 6 AM with a lunch break at 11:30 AM, that could be recorded as a
TIMESTAMP WITHOUT TIME ZONE. Again, this information is useful in a vague way but does not indicate a specific moment in time until we apply a time zone. A new day dawns earlier in the east. So the Delhi factory will be the first to open at its 6 AM. Hours later, the Düsseldorf factory begins work at its 6 AM. But the Detroit factory won't actually open until another six hours later, when its 6 AM happens.
Contrast this idea (of when the factory shift generally begins) to the historical fact of when did each factory worker clock-in to begin their shift on a particular day. The clock-in is a real moment, an actual point on the timeline. So we would record that in a column of type
TIMESTAMP WITH TIME ZONE rather than the
So, yes, there are legitimate use-cases for
TIMESTAMP WITHOUT TIME ZONE. But in my experience with business apps, they are relatively rare. In business, we tend to care about actual moments: when did the invoice actually arrive, when exactly does that contract go into effect, at what moment did that bank transaction execute. So in such common situations, we want the
TIMESTAMP WITH TIME ZONE type.
For more discussion, see my Answer to the similar Question, Should I store UTC timestamps or localtime for shifts
Note that Postgres specifically never saves the time zone information specified when inserting a timestamp.
TIMESTAMP WITH TIME ZONE
- Any specified time zone or offset included with the input data is used to adjust the value to UTC and stored. The passed zone/offset info is then discarded. Think of
TIMESTAMP WITH TIME ZONE as
TIMESTAMP WITH RESPECT FOR TIME ZONE.
- An input of 12:00 noon on March 7 this year in India will have its time-of-day adjusted to UTC by subtracting five and a half hours: 6:30 AM.
TIMESTAMP WITHOUT TIME ZONE
- Any specified time zone or offset included with the input data is entirely ignored.
- An input of 12:00 noon on March 7 this year in India is recorded as 12:00 on March 7 of this year with no adjustment.
The SQL standard barely touches on issues of date-time data types and behavior. So database vary widely in the handling of date-time.