9 ms·
Timestamps and Time Zones in PostgreSQL (2016)
- cpeterso 8y agoWhat timestamp types are available in MySQL, SQL Server, and Oracle?
- tyingq 8y agoBe careful with the MySQL ones. A couple of them overflow past 1-1-2038, which would break some use cases now. https://bugs.mysql.com/bug.php?id=12654 https://bugs.mysql.com/bug.php?id=12654
- deleted 8y ago[deleted]
- emmelaich 8y agoDunno but sqlite3 assumes times are in utc.
- userbinator 8y agoSQL Server has datetimeoffset, which computes in UTC but also stores the timezone.
- niftich 8y agoOracle has 'DATE', 'TIMESTAMP', 'TIMESTAMP WITH TIME ZONE', and 'TIMESTAMP WITH LOCAL TIME ZONE'. Out of these, 'TIMESTAMP' and 'TIMESTAMP WITH TIME ZONE' do exactly what you'd expect without needing a big tutorial. 'DATE' is a 'TIMESTAMP' that can't store fractional seconds. And 'TIMESTAMP WITH LOCAL TIMEZONE' is an unmitigated footgun that you'll probably misuse.
- mjevans 8y agoI think I slightly disagree about the choice of using timestamptz over timestamp. The main reason being that when I feed data in to the database I always want that same data back out. From the description I now realize that if I backed up and restored, or otherwise ran the same database with a different configuration the database might "try to be smart" and return a modified value. I guess to store a timestamp and a timezone I should use a timestamp a second column with the timezone value (maybe a string, but I'd like a clean way of telling the database I want an 'enum' but to dynamically just add new values as it sees them; yes for PostgreSQL I'd do that with a secondary lookup table and an insert/update trigger).
- minitech 8y agoIt would return an equivalent value, which is the important thing. You can specify `AT TIME ZONE 'UTC'` (or whichever timezone) when consuming it to get a consistent look. On the other hand, it’s not clear from just the value what point in time a `timestamp without time zone` represents. That’s why I agree that it should be used when you don’t want to represent a point in time.
- colanderman 8y agoMoreover, `AT TIME ZONE` only works correctly when used following the semantics described in the article. It will do the opposite of what you think it should if you use `timestamp without time zone` to represent an absolute time, or vice-versa.
- conradfr 8y agoI guess you're right. I have a side project that stores radios' schedules and started with timestamptz (while storing in UTC anyway) and thought I did the right thing. Then I converted part of the project to Elixir and discovered that Ecto use timestamp by default. I thought it was odd but looked into the reasoning and discovered the misnaming of the "with timezone" part and that José Valim was right as usual ;) What I ended up using in my select queries is something along the lines of : > s.date_time_start at time zone 'UTC' at time zone 'Europe/Paris' as start_at Unrelated but at work Friday (while reading this article, as fate would have it) I had to debug a erroneous time displayed to some users by text. I found one line in the service that forgot to specify the timezone while formatting the datetime. It used to work so what changed ? Well our users are increasingly international and we recently move those cron jobs from one server in Ireland to multiple AWS zones so what seemed to work for years before showed up now as a bug. I also know they had a hectic debug day six months ago during the last DST event. In conclusion, dates are hard.
- zeroimpl 8y agoI sometimes wish there was an actual "TIMESTAMP WITH TIME ZONE" type that stored the input timestamp as well. Basically, a version that didn't canonicalize the input by converting to UTC. This way, I can easily show timestamps to the user in the time zone they used to input them.
- niftich 8y agoI frequently wish that datetime libraries and APIs frozen in time and awkward design were given a second pass and redone in the style of Java 8 [1], which is a triumph of clarity after many years of lessons learned. Even putting out a new RFC would help move things along, because I've long felt that RFC 3339 is underspecified [2]. If datetime libraries were more thoroughly patterned after that of Java 8, there would be less need for the 'misconceptions programmers believe about datetimes' posts, and less need for the 'just use UTC' or 'maybe always-UTC isn't the right advice' posts, because the data-types are thoroughly reflective of their intended use, and their misuse is harder than in other APIs. [1] https://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html https://docs.oracle.com/javase/8/docs/api/java/time/package-... [2] https://news.ycombinator.com/item?id=12364805 https://news.ycombinator.com/item?id=12364805
- tofflos 8y agoI don't know. I don't necessarily think it's a bad wish but I also feel that the API has its' downsides. My impression is that it has caused a fair amount of confusion among Java developers because it forces a more complicated understanding of time upon people who in many cases aren't interested in writing applications that deal with describing events accurately across centuries or with multiple time zones. Personally I find the API confusing and arbitrary. Why include a Year class? Why include both OffsetDateTime and ZonedDateTime? What is the difference between Instant and LocalDateTime for my application that doesn't travel across time zones? Why isn't there an Range class with a start and a stop? I guess I find "just use UTC" much easier to deal with.
- twic 8y ago> My impression is that it has caused a fair amount of confusion among Java developers because it forces a more complicated understanding of time upon people who in many cases aren't interested in writing applications that deal with describing events accurately across centuries or with multiple time zones. I think this is spot on, and is the reason why it's so hard to design time libraries, particularly in language standard libraries. How do you allow all users to express what they need, while not requiring any users to express what they do not need? Bear in mind that Joda/java.time are still oversimplifying, because they take UTC as their ground truth, which means they can't account for leap seconds. A really thorough time library would take TAI as the ground truth, then have a layer which knew about leap seconds to convert to UTC [1], then a layer which knew about timezones to convert to local times. Not knowing about leap seconds means you can't calculate the time between two Instants accurately - if there is a leap second in the interval, the time will be off by one. However, since most people don't mind about gaining or losing a second every now and then, UTC is a decent tradeoff. > Personally I find the API confusing and arbitrary. Why include a Year class? So you can't accidentally pass the year and month, which would otherwise both be integers, the wrong way round to a method which takes those, as i did earlier this week. > Why include both OffsetDateTime and ZonedDateTime? There's a time format which looks like '1918-11-11T11:00+01:00'. You can parse that to an OffsetDateTime, but you don't have enough information to parse it to a ZonedDateTime. Was that Paris winter time, or Lagos standard time? If you wanted to find the exact six-month anniversary of that moment, you would need to know, because it would be at 11:00 local time [2], which could be 1919-05-11T11:00+02:00 in Paris but 1919-05-11T11:00+01:00 in Lagos, and those are different moments in time. > What is the difference between Instant and LocalDateTime for my application that doesn't travel across time zones? If your application, or one of its users, resides in one of the timezones which have summer and winter times, then you still have two offsets to think about. Every Instant identifies a unique moment in time for you (up to leap seconds!), but a LocalDateTime does not, because there is one hour a year of LocalDateTime values which identify two different moments an hour apart, and one hour a year of values which don't identify anything at all. [1] inb4 someone goes off about relativity [2] because everyone knows that the armistice was signed at 11 o'clock on the 11th day of the 11th month, so if you rock up and say "bonjour mes amis, le cérémonie de l'anniversaire c'est à 10h" you will get bayonetted
- jasonl99 8y agoThat is a phenomenal article about timezones in general that apply to any database, as well as a great explanation of features specific to postgres. It is interesting that a timestamp without timezone is internally represented a timestamptz at UTC. Postgres is also has some great functionality converting between timezones: https://www.postgresql.org/docs/11/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT https://www.postgresql.org/docs/11/functions-datetime.html#F...
- zzzeek 8y agoI completely disagree with this advice: > However, when you come across timestamp, you can’t possibly know in what time zone the timestamp is just by looking at it. yes I do, it's in UTC, because I assume the application is written to correctly handle datetimes. > It is merely a wall time. Maybe it’s UTC, no, it's UTC. > or maybe the developer stored it in local time. right, maybe the application was not programmed correctly, that is always a possibility. But if they are using the timestamp with timezone datatype, it is 100% more likely they have made mistakes in doing so because it is much more confusing to have to fight against PostgreSQL's assumptions and implicit conversions, as the complexity of its behaviors and decisions using this datatype are described in this article. Does the application come up with the wrong answer if the server it runs on, or the webserver container, is mistakenly set to Eastern Timezone rather than UTC? Is the application ensuring that it knows the correct timezone of timestamps as they enter the application, and if so, why isn't it converting to UTC at that moment? Is the application otherwise receiving datetimes of arbitrary timezone, letting them move around business and display logic in their raw form, and waiting until they enter the database to be correctly normalized? that's too late. A correctly coded application does explicit normalization of datetime values the moment they enter the boundaries of the application, just like it would also convert encoded bytes into a unicode-aware strings up front, so that within the application there is no ambiguity, both when persisting and when handling.
- grzm 8y ago> "because I assume the application is written to correctly handle datetimes." > "it is 100% more likely they have made mistakes in doing so because it is much more confusing to have to fight against PostgreSQL's assumptions and implicit conversions" If you're assuming the application is correctly written to handle datetimes, you can safely assume it's also correctly using time zone aware date time data types, and using their database driver correctly, so there's no worry about PostgreSQL's assumptions or implicit conversions. Datetime handling is hard. However, most of the issues you're talking about here are independent of whether you're storing instants as timestamp or timestamptz in PostgreSQL. Persistence is only one layer in an application stack. As an aside, I haven't looked, but if someone hasn't already created a "Zoned Datetime" datatype extension for Postgres, it'd certainly be interesting to try.
- robbiep 8y agoI have a question, that was partially addressed in some comments in the last Timezone in Databases article earlier in the week - What do people think about using Unix time in databases?
- paulryanrogers 8y agoDirectly using Unix timestamps is unlikely to save much space since most internal representations are likely similar. They're also difficult for mortals to read and change in human-significant ways (like 3 calendar days ago).
- TheAceOfHearts 8y agoIt depends on your use-case. You should also be aware of the Year 2038 problem [0]. What are you trying to achieve? [0] https://en.wikipedia.org/wiki/Year_2038_problem https://en.wikipedia.org/wiki/Year_2038_problem
- robbiep 8y agoLargely timestamping. Accuracy to a high level (leap seconds) is irrelevant. Aware of 2038 problem, assume we will have some fun along the way to there along with the rest of the unix world!
- TheAceOfHearts 8y agoStoring Unix timestamps in a 64-bit signed integer field would probably be fine in that situation. The only downsides that come to mind are that you'd have to manually convert the number to an actual date when using it, and that you'd lose easy access to date-related functionality in the database.
- jeltz 8y agoBut also totally pointless since PostgreSQL's timestamptz type also uses 64 bits.
- 8y ago
- andynrkri 8y agoThanks for this. Was very helpful!
- sudhirj 8y agoDoesn’t this mean that the default Rails treatment of time stamps (created and updated at) in Postgres is wrong? Any plans to change that?
- msla 8y agoI'd be happy with everyone being extremely clear about the two kinds of time: Wallclock-time and duration-time. What's the difference? Duration-time does not have leap-seconds. I'll say it again: Duration-time does not have leap-seconds. Once more: Duration-time does not have leap-seconds. For duration-time without leap-seconds, give me TAI: > International Atomic Time (TAI) is a statistical atomic time scale based on a large number of clocks operating at standards laboratories around the world that is maintained by the Bureau International des Poids et Mesures; its unit interval is exactly one SI second at sea level. The origin of TAI is such that UT1-TAI is approximately 0 (zero) on January 1, 1958. TAI is not adjusted for leap seconds. It is recommended by the BIPM that systems which cannot handle leapseconds use TAI instead. https://tycho.usno.navy.mil/leapsec.html https://tycho.usno.navy.mil/leapsec.html Document that it is TAI. Include in the documentation that TAI does not include leap-seconds. Thank you.
- deleted 8y ago[deleted]
- gsich 8y agoWhy do you call it "duration-time"? It seems obvious that for an event that lasted 5.43s for example, (a "duration", better wording) that it's not counted with leap-seconds.
- msla 8y ago> Why do you call it "duration-time"? Because it has units of time (seconds, often) and it's done using time-stamps, as in begin-time and end-time. > It seems obvious that for an event that lasted 5.43s for example, (a "duration", better wording) that it's not counted with leap-seconds. Obvious until you try to get someone to understand why their time-stamps can't be UTC if they're going to be used for duration-time instead of wallclock-time. Obvious until you try to get a library or language implementer to understand the UTC isn't Universal.
- gsich 8y ago>Because it has units of time (seconds, often) and it's done using time-stamps, as in begin-time and end-time. If you are recording a duration you have another unit. If you use begin-time and end-time you are calculating a duration, which is different of course. If you calculate durations, then you might consider leap seconds, if they are important for your measurement. If not, then you just ignore them. I don't see a problem here. Besides, leap-seconds are known, so removing them from a calculated duration is not a problem.
- welder 8y ago> A Valid Use Case For timestamp without time zone This should be "A Valid Use Case For timestamp with time zone". This article gets it all wrong. The only time it's acceptable to store time with time zone is DATES that do not have a TIME component.
- groestl 8y agoI'm probably just misunderstanding, but a calendar like Google Calendar definitely needs to store time with a time zone (i.e. dates with a time component). This allows to keep a scheduled event at 9 AM, even if the time zone offset is redefined (due to a political event).
- gigatexal 8y agoWhat I like about this article is that it does something subtle yet profound: it uses repetition to solidify a concept to the reader. For example the referencing of wall time in the first few paragraphs lowers the load for the reader to refer back to previous paragraphs to reconnect with a newly introduced concept (walltime in this context was new to me as of reading this article). I think this is a huge win as it doesn’t break the reading timeline and it’s a subtle yet huge showing of attention to detail. Much kudos to the author.
- fphilipe 8y agoThanks, much appreciated. At least for me, writing an article like that takes time and lots of thought (plus forces you to grok the stuff deeply). I wish I could write more often (or at least, carve out the time and energy for it), haven’t written much since.
- gigatexal 8y agoIt reads like a senior dev helping a junior where the senior dev understands the crap he or she went through to get to where they are so they break things down and use things like repetition until something sticks to help the junior dev grok it until there’s an aha moment. Keep it up!
- yosamino 8y agoAnd thank you so much for the chart[0] mapping absolute to wall time. I always have a hard time visualizing DST changes ( usually I am mentally "sliding" bars of time against each other and get confused), but that one just gave me a click. [0] https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/map.svg https://phili.pe/posts/timestamps-and-time-zones-in-postgres...
- manigandham 8y agoAs many other comments state, the most practical advice is to just use 'timestamp' and ensure that all applications always use UTC. The only exception is user-facing appointments that are scheduled in that user's timezone which can't be converted to UTC safely. In this case, store the timezone in a separate column specifically for those rows/entities that need it. In neither case is timezonetz useful or necessary, and will actually end up causing more bugs by converting timezones to/from the database instead of just returning the value and letting your application deal with it.
- mmaurizi 8y agoEven better for future dates than storing the timezone is storing the geographic point the appointment is at and determining the timezone when necessary - as timezone boundaries can change.
- taffer 8y agoI think you misunderstood how timestamps work in PostgreSQL. Timestamptz is always saved as UTC. Only in exceptional cases where you need to store something in local time, such as a scheduled event that is not in UTC, you should use the timestamp WITHOUT TZ. This is also what well-known PostgreSQL developers recommend: https://tapoueh.org/blog/2018/04/postgresql-data-types-date-timestamp-and-time-zones/ https://tapoueh.org/blog/2018/04/postgresql-data-types-date-...
- manigandham 8y agoIt's not about the storage of the value, it's the fact that PG will potentially change the value you provide compared to what's stored when using 'timestamptz'. If you use UTC and send it as UTC and request it as UTC then everything is fine but there are plenty of edge cases when using ORMs, handwritten SQL, different server timezones, or type conversions that can lead to issues on the way in and out of the database. Having the database store anything other than what you provide is always a source of bugs.