All these visits by Santa happen at different moments is time, yet all happened shortly after midnight, per each locality’s own midnight. Much later still is midnight in Paris FR, and still later midnight in Montreal CA. Then the reindeer move on in a westerly direction, reaching India at its midnight which happens to occur several hours after that Auckland midnight. Then he works his way westward, as the next midnight happens, soon reaching the Philippines. Early in Santa’s day he visits Auckland NZ just after midnight, as that is one of the earliest midnights on the globe.
To record the fact that Christmas starts after midnight on December 25 this year, we need to say 00:00:00 without any time zone. Table: holiday_Ĭolumn: start_ Type: TIMESTAMP WITHOUT TIME ZONE Ex: Christmasįor example, say you need to record the start of holidays/holy days. They have no real meaning until you apply a time zone or offset-from-UTC. 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). TIMESTAMP WITHOUT TIME ZONE values are not a point on the timeline, not actual moments. For tracking moments, specific points on the timeline, always use TIMESTAMP WITH TIME ZONE, not WITHOUT.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).In common business apps this type would only be used for:.There are use-cases for TIMESTAMP WITHOUT 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. 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. With all that said, I only have one more suggestion. 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.
#Postgresql current timestamp update#
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.
The most common rule that changes is about the adoption or not of day light saving time (DST).įor example, imagine that you are at, let's say, (not yet in DST) schedule some event to happen at 10:00 (which would be already in DST), and at 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 11:00 (instead of 10:00), that if you use timestamp WITH time zone, and keep your TZ configurations up-to-date. 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.
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. Now, with those two down, I can came with only one good reason to use timestamp WITHOUT time zone. 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). 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). 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. An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. What it does is to store every data in UTC time zone, as stated in the docs:įor timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). 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.
0 kommentar(er)
