? Footguns as a synonym for "caveat" or "gotcha" were in use long before LLMs. I hate Claude speak too but there's no need to knee-jerk blame everything on LLMs.
I think most of the confusuion comes from "AT TIME ZONE" being the syntax for both the conversion from _and_ to timezone'd timestamps. Maybe it would have been more intuitive had the two operations gotten different wordings, e.g.:
timestamp to timestamptz: AS ZONED AT TIME ZONE ...
timestamptz to timestamp: AS LOCAL AT TIME ZONE ...
Postgres 'at time zone' is confusing, but it's internally consistent. Once you understand what it's doing, you can work with it and it will reliably behave the way it's designed to.
The SQL standard is unfortunately really horrible when it comes to handling of time. The type `timestamp` is not a timestamp at all because it doesn't encode a unique point in time, it just stores a date and a time which has to be interpreted relative to a timezone. It should be called "datetime".
Moving a Java Instant back and forth between a database is also a surprisingly difficult task to do right, and it doesn't help that JDBC is just handling it completely wrong if you use its setTimestamp/getTimestamp methods. Not because it is a bad design with footguns, but because the implementation is just plain wrong and will corrupt your data if you deal with instants whose calendar date is far enough in the past due to it using the legacy date/time API which switches to the Gregorian calendar for dates in the past.
The name `timestamp with time zone` is also misleading because it doesn't actually store a time zone, it stores the number of seconds since epoch like a java.time.Instant (although at a different resolution). The "with time zone" part just refers to the textual format you denote the values in which includes the time zone after the date/time part to uniquely identify a timestamp.
A ZonedDateTime is not an instant and a timezone, for the same reason that you can’t unambiguously round trip between arbitrary timezones and UTC:
- zoned datetimes carry ambiguities as to their actual location on the timeline (because they can repeat, or not exist at all)
- future zoned datetime carry outright uncertainty as to their actual location on the timeline (as the zone’s offset can be updated at any point and any number of times until the event has elapsed)
Has anyone proposed versioning timezones? Or is this such an edge case it would be overkill? (Either specify your future instant in UTC if you mean to stick to that, or specify it in a timezone and accept that it could change before it happens, or if you need something else get it in a contract and don't trust the computer!)
The point of using zoned events is to match the life and expectation of people living in the real world e.g. if there is a meeting in Perth at 10AM, and the Perth timezone gets shifted, the meeting is still occurring at 10AM perth time. If it’s broadcast then every other time is what changes (or not).
If people want to fix their meeting internationally they can already do that by setting their meeting time in UTC.
> future zoned datetime carry outright uncertainty as to their actual location on the timeline (as the zone’s offset can be updated at any point and any number of times until the event has elapsed)
I wondered if it would be worth applying a (optional?) version to a timezone when stored, so you could distinguish between "whenever it is this time in Perth" vs "when I currently think this time will be in Perth, though if Perth changes its mind on how it offsets time, I want to keep what time I currently think that will be".
But you're right, that doesn't really add anything over storing it as UTC.
The fix is storing the datetime without a timezone, so the hour stays stable, no matter what the timezone of Perth does, plus optionally and separately the location (Perth), which could be stored as the timezone id of the location. But you could also use coordinates. Anything which can be mapped to its timezone later works. Now the time will never change for the original location (sic), and you can always compute the time for any timezone in the world, because in the instance of computation, you take the current valid timezone of the location, and you apply that to the stable datetime. Of course a current computation of that might have a different value in the future. But that doesn't matter, since the time for the original location (sic) never changes, and the value for different timezones is supposed to change.
> if there is a meeting in Perth at 10AM, and the Perth timezone gets shifted, the meeting is still occurring at 10AM perth time.
The thing about this is that if Perth's time zone ever changes so erratically or with such little notice that participants need to be notified that the point-in-time of an upcoming meeting has changed, it's no longer clear whether the participants would actually want the zoned "Perth at 10am" time to be canonical.
If athletes were flying in from around the world for an international competition tomorrow at 10am, and Australia decided to increase Perth's UTC offset by 1 hour as of today, would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?
> The thing about this is that if Perth's time zone ever changes so erratically or with such little notice that participants need to be notified that the point-in-time of an upcoming meeting has changed
"erratically" and "with little notice" are pretty relative when it comes to timezone shifts. DST decisions have been made with as little as a week lead time, Samoa dropping an entire day off of its calendar was done with under a year lead time (noises started about 9 months prior, the act was assented 6 months prior).
> would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?
There is actually a simple heuristic you can use: if a point in time should be sticky to a calendar (e.g. calendar app or appointments which need to be synchronized between multiple humans or parties for a given context/location/region), store a datetime _without_ a timezone and make the timezone configurable for the user/infer it from the user. If you want a point in time which will not "physically" change, store a datetime _with_ a timezone, always, preferably UTC (e.g. logging, timers, measuring the occurrence of events).
You're still just talking about client conversion on the write instead of the read. A Unix epoch is a UTC timestamp. That number is not time-zone-less it's just in the default computer time zone.
The 2 different types of datetimes encode different information and is not purely a display/presentation issue, it's also storage issue.
I happen to call them "scientific datetime" vs "cultural/political datetime". However, the software dev industry has not converged on a standard vocabulary to delineate the 2 types which is unfortunate because programmers are unaware that the difference exists.
But this is still different to a time someone enters into a calendar. Standard time changes its offset (to UTC) over time, while a time in a calendar is fixed in the nominal sense.
"scientific datetime" is quite ambiguous, since I would consider science-level precision time to be TAI (International Atomic Time, what UTC uses as a reference), or maybe UT1, which is one variant of UT (Universal Time, unrelated to UTC), depending on the scientific field. For simple cases, UTC might be enough, so you could call this "UTC".
I think practically what matters for developers are three things:
- Standard time (dependent on timezone)
- UTC (the reference for standard times in the different timezones)
- Calendar times, just referring to a specific date and time, usually independent from both standard time and UTC, from the author's perspective (others viewing a foreign calendar might see times interpreted in their own timezone). Often scoped by physical location, but not necessarily.
, while a time in a calendar is fixed in the nominal sense.
The above scenario of fixed time regardless of DST/TZ changes is what I tried to call "cultural/political time". In other comments, I called it "appointment time".
What you call "calendar time", others will call it "time with calculated UTC offset". (Which then leads to more meta discussion of "no... calendar time is not UTC offset because ..." )
Both examples of ambiguous labels causing more confusion is prime example of the industry not converging on good names to make devs aware of the difference.
>I think practically what matters for developers are three things:
That categorization is fine but is still obscuring the key issue: many developers think they can collapse all of your 3 types into one simple strategy of "always store it as UTC"
The trouble is that you can't really make safe assumptions about whether to use the "sticky" paradigm or the "point-in-time" paradigm.
Sticky really only makes sense in two scenarios:
1. when all participants are assumed to be in the same geographic/political time zone for the foreseeable future, or
2. when there's some privileged participant such that everyone else can assume events follow that participant's time zone (e.g. a company headquarters that moves very rarely, or an individual's personal wakeup alarms which can probably be assumed to follow their current location's time zone as they travel).
If you have a group of friends who like to stay in touch with regular group calls, and all/most of them are digital nomads who change their time zone of residence multiple times per year, you probably don't want the sticky paradigm.
In practice, if you have participants from different timezones, you agree on one "reference" timezone, which can be also UTC, as in your nomads example. You can consider UTC as just another calendar you can stick to (but you should just not hardcode this in software for this appointment use-case). You actually need a reference to be able to plan an event in the first place. Otherwise you don't know which time you can propose. You would need to propose a "fixed" time for every timezone which participates, but that doesn't work, because these times might not refer to the same physical instant, so the meeting would not be (fully) synchronized. So instead, you take some time from some timezone and translate that to all the other timezones.
You can even see that in online games, where some of them have an official "server time", which is globally the same, helping players to meet at the same time instant. UTC itself is another examples of this, used e.g. for global navigation or air traffic control.
That "only advantage" is doing a lot to dismiss the actual use case of, say, everyone keeping appointments on their calendar when the legislature passes laws around time zone changes.
> If you want a point in time which will not "physically" change, store a datetime _with_ a timezone, always, preferably UTC (e.g. logging, timers, measuring the occurrence of events).
This works for immutably recording the current time into a log, yes.
For much else (e.g. a timer still-to-come that should go off in “1000 days”), leap seconds break this.
You could store such time using TAI as the timezone (TAI is UTC without the leap seconds), if RDBMSes actually persisted the timezone. But they don’t. They’ll just convert back to UTC at point of write.
I have a feeling that most people who really need to solve this problem end up using a (pos, len) column pair where `pos` is the current UTC time when the future-event was registered, and `len` is an interval representing how far away it is in monotonic time — either as a difference of POSIX timestamps at time of evaluation, or as a SQL INTERVAL, etc.
tzdb is versioned, but I can't recall if the individual timezones carry a version though...
One way to manage is to store the datetimes with a timezone identifier and the offset, and when you load a new tzdb, go through and validate that the calculated offset matches the stored offset... for those events where they don't match, you have an exciting challenge of figuring out if the event should stay with the time zone or stay with the offset; both answers may be right ... ideally you inform the user(s) about what you've done and allow them to fix things software has messed up.
Relational databases have exactly 1 type that corresponds to modern data-handling practices: timestamp with time zone, that stores a timestamp. There is no good way to store any other modern type, and the 1980s practices on time handling weren't actually very good.
I'd say relational databases (in the sense of standard SQL) have 0 types that correspond to modern data-handling practices: timestamp with timezone stores an instant but lies about it and implicitly gets converted from and to the connection-local timezone.
It's always worth noting that future UTC timestamps are also ambiguous for certain operations, most notably computing durations, due to the unpredictability of leap seconds.
Except you shouldn't do the GMT/PST part, because it can lead to people falsely believing that Postgres can handle time zones, leading to silent data corruption when the time zone definition changes - such as due to abolishing DST.
Postgres can only store 1) unzoned local time, or 2) UTC, with optional conversion on read/write.
Rookie pseudo-footgun - assumed skill exceeds capability error - usually accompanied with blameshifting pronunciation as demonstrated here. Anyone familiar with UTC/tz from another platform (e.g. excel) would be mostly immune or able to quickly identify and remedy.
People who handle this footgun properly tend to come from two groups:
- Those with safety training for this class of foot-pointed firearms, who know how dumb shit looks like and that they should not do it;
- Those with officer training who are able to recognize the higher-level categories, and take principled approach to safety - e.g. recognizing that "date", "timestamp, "duration", "time of day", "time of week", etc. are different concepts and should not be mixed.
> Adding a month with + INTERVAL '1 months' is timezone-dependent. […]
Adding months isn’t well-defined anyway, even when using date, for days of month > 28. I think it’s a mistake that systems generically allow such a computation (as opposed to application code implementing domain-specific business rules).
| Because the length of a month or year changes from one month or year to the next, ambiguities can arise when shifting a date by months and/or years. For example, what is the date one year after 2024-02-29? Is it 2025-02-28 or 2025-03-01? Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve this ambiguity, so the "ceiling" and "floor" modifiers (14 and 15) are available to let the programmer decide. If the next modifier after a time shift is "ceiling", then any ambiguity in the date is resolved by choosing the later date. The "floor" modifier resolves ambiguities by resolving to the last day of the previous month. The default behavior is "ceiling".
It’s not just a matter of there being different ways to do it, it’s also that none of the ways obey expected arithmetic laws like associativity. Adding four months and then subtracting four months can yield a different day than the starting point. Or adding three months and then one month can yield a different date than adding four months at once.
the greater fun to be had is doing integration between two systems with differing ideas of how to store time. My experience was with big erp that stored utc and little country store hack that stored local time. I sorta learned why I like utc, but there are plenty of interesting gotchas with time thats for sure!
The more I read about pgsql, the more I wonder why I would want to switch to it...
Granted, mariadb has weird time things too... but thats the tip of the iceberg between autoincrement with vacuum, vacuum in general, and that thread from the other day about bad migrations/version upgrades...
I don't rely on time calculations at the database level. I handle them in the application based on UTC time stored in the database and the user's time zone. This shifts the problem to the application code, where I can control it precisely and make conscious decisions about how to handle specific business requirements, such as when a day ends or how to deal with events across different time zones. It also makes it possible to properly test all cases with unit tests.
We can think of the split between DB and application in terms of DX or code, or we can think of it in terms of colocation of data and compute. The latter case will be compelling sometimes.
I've seen too many ways storing everything as UTC goes wrong/gets confusing. Round-tripping at least UTC offset (which unfortunately Postgres `timestamptz` in this case does not do and is effectively the same as storing as UTC) gives you more debugging tools for events in the past and more opportunities to do the right thing for future events in worst cases (strange, unexpected DST shifts). The best option is if you can also round trip exact time zones such as IANA strings like "America/Los_Angeles". Even fewer databases support that natively right now.
When UTC offsets roundtrip you can do all your date math as if everything was in UTC, but still not lose information from the user about what time they thought an event occurred at or might next occur at.
t depends on the system. Keeping a time zone alongside a UTC timestamp may be useful in some cases.
For example, if your system is supposed to remind a user to do something, such as take a pill, and the user changes time zones while travelling, you may want the reminder to occur at 9:00 local time wherever they currently are. In that case, storing the original time zone together with the event may not be necessary, because the relevant time zone is the user's current one.
Everything depends on the context. For me, however, there is no doubt about one thing: I store timestamps in UTC. Whether I also store a time zone, and where I store it, depends on the system and its business requirements.
Keeping a UTC offset is two extra bytes and with a UTC offset you can do all of your time comparisons and time math as if everything was in UTC, but you have extra debugging information in the UTC offset. It's still sometimes useful to store a time zone as offsets to timezones certainly are not 1:1 (esp. with DST math), but some places like logs and past dates UTC offset is sufficient and you don't even need to store timezone.
Postgres doesn't a native type that supports UTC offsets, but some other databases do and it is extremely useful. At this point in my career, I would never choose UTC storage over UTC Offset storage.
Ruby on Rails would agree with you. I've been working in web development for a decade now, across a couple of languages and frameworks, and I've only ever stored UTC. I currently work a lot with time-series data, and I'd rather not know what hack makes it possible to track events that occur during a DST transition.
The text says: "Comparing a timestamp and timestamptz will always result in false."
That is wrong. Postgres changes the timestamp to timestamptz very quietly in the background.
Wether it uses the time zone of the session for this.
If it is true or false depends on the TimeZone setting. This is more bad than "always false".
In production with UTC it works. On a laptop of a California developer it does not work.
Just tested:
SET TIME ZONE 'America/Los_Angeles';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* f */
SET TIME ZONE 'UTC';
SELECT '2026-03-01 00:00:00'::timestamp = '2026-03-01 00:00:00+00'::timestamptz; /* t */
If you can, use Postgres 16.
The double AT TIME ZONE 'UTC' thing from the text is not needed there. There you have date_add with a time zone as the third argument:
Automatic conversion using "global" state between "local"/"human" time (5pm where I am now) and points in time (ie with timezone) is one of the biggest sins imho for many libraries/db's/languages. Ran into it a bunch of times using C# as well.
It made sense when databases and programs were used almost exclusively locally. It still makes sense for local apps (e.g. local-first or local-only smartphone and desktop apps) who typically will automatically do the right thing that way based on the OS regional settings.
It only started causing widespread issues with the rise of cross-region internet SaaS. Database systems, language runtimes, and OS APIs are keeping the default behavior for backwards compatibility.
Yes and no, it was thought to make sense for "end-user-programmers" where it's helpful to be fully locale specific, I'm Swedish and my OS settings makes programs expecting comma (,) signs for decimal separation is something that's actually hit me today when copy-pasting between programs.
So in practice, while it was kinda useful to be locale/region dependant for some users it's probably been more trouble in the long run to be overly helpful.
C# was released after y2k, so they don't have the excuse.
Also, You're missing the biggest sin here however, locale specific time is OK, automatically allowing conversions/comparisons to points in time types without specifying timezones has in principle never caused anything but grief.
> timestamp doesn't contain the timezone information. Technically, it doesn't represent a time in the real world.
Unfortunately this is just as misleading.
It's correct that `timestamp` doesn't contain timezone information, but *neither does `timestamptz`. All `timestamptz` is is UNIX-style Epoch timestamp, that is an absolute point in time in UTC. It says nothing about the timezone because there are no time zones in that context.
The confusion arises because when you insert into a field you can insert "19:00 on the 29th March 2025 in London", but this is simply converted to UTC at insert time and the timezone is lost.
If anything, `timestamp` does represent a time in the "real world" and `timestamptz` represents a time in the universe.
If time zones are important to you, you need to record the time zone in a separate field. That is all. Where you store this depends on context, of course. You could store the user's current time zone in a profile and render all times in that time zone (psql and other clients do this automatically based on the OS time zone btw). Or you could store the time zone with each record if that makes sense, ie. you want to know what the wall clock time was for the user at the time the record was taken.
This is why you store two dates: what the user sent and what you need for calculations. What the user sent becomes your oracle and what you show the user (date time + timezone) and your derived utc to do datetime arithmetic
The part that keeps biting teams is that AT TIME ZONE is a cast, not an annotation. On a timestamp it means "interpret these wall-clock digits as this zone and produce a timestamptz". On a timestamptz it means "render this instant as wall-clock digits in this zone and produce a timestamp". Same syntax, opposite direction, and the session TimeZone is the hidden third argument.
That is also why calendar math and instant math disagree. interval '1 day' on a timestamptz is 24 hours, so a 09:00 local appointment drifts across DST. The usual fix is to strip to timestamp in the civil zone, add the calendar interval, then cast back. The double AT TIME ZONE 'UTC' in the post is that pattern with UTC as the civil zone, which only works if the civil zone really is UTC.
What I have settled on: store events as timestamptz, force TimeZone=UTC on every connection (app, migrations, replicas, psql), and convert to a named zone only at the edge. timestamp without time zone is fine for things that are not instants (a store's opening hours, a birthday) and a footgun for anything that is.
I love how there’s even errors in the article. Timestamps and time zones are so subtle and nuanced, and people assume they are WAY simpler than they really are. Even smart engineers.
My experience is (despite the Postgres developer's advice to use it), timestamptz is largely a useless data type since it is just a wrapper around conversion to UTC.
- Are you storing past events? Just store them as UTC. Maybe you use timestamptz to do it for you, but it's actually a more obtuse interface for that than timesstamp
- Are you storing future UTC times? Great, just use UTC, see above
- Are you storing future human times? Then timestamptz is actively harmful because it eagerly converts to UTC so even if you get an updated tzdb in time for when the event comes due, you don't know what happened at write time so now your datetime is ambiguous. It's less broken to use a plain timestamp + string timezone column (if you need to sort by it, maybe a denormalized _utc column too, with the understanding that you'll need to regenerate it or accept slight off-by-one errors when you update the tzdb, but at least you can do this when you know what the input value was, unlike with timestamptz)
SQL Server has `datetimeoffset` which stores the UTC offset (and so roundtrips it), which is definitely an improvement. (You still may need/want a string timezone column even with UTC offsets, but you don't need a denormalized _utc column because datetimeoffset math just works as if the times were all UTC.) It seems like something that Postgres could use. Especially because the overhead for storing UTC offsets isn't that much. (10 bytes versus 8.)
I think poster overfits the postgres wiki advise. The thing is, most timestamps are not "UTC". That's only when you want to print an epoch value human readable you need some timezone and UTC is the most neutral. That doesn't mean you should opt for zoned timestamps in columns.
Because of this overfitting OP lands on exactly the wrong advice. Unfortunately the quoted line is "Even Postgres Wiki says: Don't use timestamp without time zone" but wiki says "Don't use timestamp (without time zone) *to store UTC times*".
> Don't use the timestamp type to store timestamps, use timestamptz (also known as timestamp with time zone) instead.
> Why not?
> timestamptz records a single moment in time. Despite what the name says it doesn't store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it'll store the point in time that value describes. By default it will display times in your current timezone, but you can use at time zone to display it in other time zones.
> Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.
> timestamp (also known as timestamp without time zone) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don't know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.
> So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.
This summer, with the help of AI, I found an inconsistency in the way Postgres handles timestamp vs. timestamptz comparisons under a DST spring-forward gap for the datetime_ops btree family [0]. Essentially there are scenarios where expression B > A and B < C, but also C = A, which can cause queries using a btree index (among other things) to return an incorrect result.
The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.
> Backpatching a behavioral change like this seems awfully scary.
For the moment I'm just contemplating what we could potentially
change in master. So far I don't like any of the choices :-(
The problem really is inherent to DST itself, just as the month math in TFA is inherently wonky in any system. What's January 30th + 1 month? February 28th (or 29th, if a leap year)? March 1st? March 2nd?
UI time elements have to be presented in the user's TZ. In the DB one should store timestamptz in UTC for all things, and maybe also timestamptz in non-UTC TZs for user input (e.g., in a calendaring app).
Unless I'm really misunderstanding, this is just "footgun of timestamp without timezone and implicit coercion"?
The only purpose of the naive timestamp is to defer a necessary step of converting a sort of nominal prototype or template to a real moment on the timeline. Until you pin them down, they don't really represent moments.
It's like having a NULL in the timezone slot. I think it should mean "unknown, anything goes on a per-value basis!", rather than treating it like some polymorphic type that gets magically parameterized at runtime.
IMHO, it is a mistake of the standard and PostgreSQL authors to try to enable such sloppy thinking by users and applications. Ordering relations shouldn't even be implemented on naive timestamps. It should be a type error, not a trigger for implicit coercion.
Perhaps it should even be a domain over text or some composite type that represents the partially populated time info. Require explicit mutation to populate the missing bits and allow conversion to a well-defined moment.
I think a sane application should only use the timezone-aware timestamp for storage, and explicitly manage its own "timestamp templates" and conversions before trying to do comparisons on the timeline.
Edit to add: I think you can say the same about timeline versus some timestamp-with-timezone strings. Make it more explicit that the ordered type is normalized moments. Make sure there is a normalizable external representation like ISO timestamps.
Make it clear that other representations are not stable. E.g. any legal timezone that could have its definitions change over time is not a stable concept to use in a representation of a moment. It is also effectively naive unless it includes another version parameter to state which version of the legal definition is intended.
>AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp
surprising and a foot gun. Asking for at a timezone stripping the timezone makes no sense to me. Having `AT TIME ZONE 'UTC'` produce a timestamptz at +0:00 seems not insane.
In nearly all cases, I recommend people use TIMESTAMPTZ with UTC timezone (i.e. convert on the way in). If possible, operate your apps with UTC until it hits a user's eyes, i.e. internally define your "day" to start in ~Greenwich UK.
This saves a __lot__ of headaches, of which timestamp vs timestamptz is the tip of the iceberg.
91 comments
[ 2.8 ms ] story [ 71.2 ms ] threadwhat about the load bearing real unlocks of the footguns?!??!?
https://i.programmerhumor.io/2026/09/09db9203bc618d8704855e1...
timestamp to timestamptz: AS ZONED AT TIME ZONE ...
timestamptz to timestamp: AS LOCAL AT TIME ZONE ...
https://oneuptime.com/blog/post/2026-01-25-postgresql-timezo...
Moving a Java Instant back and forth between a database is also a surprisingly difficult task to do right, and it doesn't help that JDBC is just handling it completely wrong if you use its setTimestamp/getTimestamp methods. Not because it is a bad design with footguns, but because the implementation is just plain wrong and will corrupt your data if you deal with instants whose calendar date is far enough in the past due to it using the legacy date/time API which switches to the Gregorian calendar for dates in the past.
The name `timestamp with time zone` is also misleading because it doesn't actually store a time zone, it stores the number of seconds since epoch like a java.time.Instant (although at a different resolution). The "with time zone" part just refers to the textual format you denote the values in which includes the time zone after the date/time part to uniquely identify a timestamp.
- zoned datetimes carry ambiguities as to their actual location on the timeline (because they can repeat, or not exist at all)
- future zoned datetime carry outright uncertainty as to their actual location on the timeline (as the zone’s offset can be updated at any point and any number of times until the event has elapsed)
The point of using zoned events is to match the life and expectation of people living in the real world e.g. if there is a meeting in Perth at 10AM, and the Perth timezone gets shifted, the meeting is still occurring at 10AM perth time. If it’s broadcast then every other time is what changes (or not).
If people want to fix their meeting internationally they can already do that by setting their meeting time in UTC.
> future zoned datetime carry outright uncertainty as to their actual location on the timeline (as the zone’s offset can be updated at any point and any number of times until the event has elapsed)
I wondered if it would be worth applying a (optional?) version to a timezone when stored, so you could distinguish between "whenever it is this time in Perth" vs "when I currently think this time will be in Perth, though if Perth changes its mind on how it offsets time, I want to keep what time I currently think that will be".
But you're right, that doesn't really add anything over storing it as UTC.
The thing about this is that if Perth's time zone ever changes so erratically or with such little notice that participants need to be notified that the point-in-time of an upcoming meeting has changed, it's no longer clear whether the participants would actually want the zoned "Perth at 10am" time to be canonical.
If athletes were flying in from around the world for an international competition tomorrow at 10am, and Australia decided to increase Perth's UTC offset by 1 hour as of today, would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?
"erratically" and "with little notice" are pretty relative when it comes to timezone shifts. DST decisions have been made with as little as a week lead time, Samoa dropping an entire day off of its calendar was done with under a year lead time (noises started about 9 months prior, the act was assented 6 months prior).
> would we expect all athletes, organizers, fans, etc. to show up and do everything 1 hour earlier (by solar time)?
Usually yes.
I happen to call them "scientific datetime" vs "cultural/political datetime". However, the software dev industry has not converged on a standard vocabulary to delineate the 2 types which is unfortunate because programmers are unaware that the difference exists.
If a programmer doesn't understand how the 2 datetimes behave diffenrently, they will create software bugs as I've outlined before: https://news.ycombinator.com/item?id=39418897
https://en.wikipedia.org/wiki/Standard_time
But this is still different to a time someone enters into a calendar. Standard time changes its offset (to UTC) over time, while a time in a calendar is fixed in the nominal sense.
"scientific datetime" is quite ambiguous, since I would consider science-level precision time to be TAI (International Atomic Time, what UTC uses as a reference), or maybe UT1, which is one variant of UT (Universal Time, unrelated to UTC), depending on the scientific field. For simple cases, UTC might be enough, so you could call this "UTC".
I think practically what matters for developers are three things:
- Standard time (dependent on timezone)
- UTC (the reference for standard times in the different timezones)
- Calendar times, just referring to a specific date and time, usually independent from both standard time and UTC, from the author's perspective (others viewing a foreign calendar might see times interpreted in their own timezone). Often scoped by physical location, but not necessarily.
The above scenario of fixed time regardless of DST/TZ changes is what I tried to call "cultural/political time". In other comments, I called it "appointment time".
What you call "calendar time", others will call it "time with calculated UTC offset". (Which then leads to more meta discussion of "no... calendar time is not UTC offset because ..." )
Both examples of ambiguous labels causing more confusion is prime example of the industry not converging on good names to make devs aware of the difference.
>I think practically what matters for developers are three things:
That categorization is fine but is still obscuring the key issue: many developers think they can collapse all of your 3 types into one simple strategy of "always store it as UTC"
Sticky really only makes sense in two scenarios:
1. when all participants are assumed to be in the same geographic/political time zone for the foreseeable future, or
2. when there's some privileged participant such that everyone else can assume events follow that participant's time zone (e.g. a company headquarters that moves very rarely, or an individual's personal wakeup alarms which can probably be assumed to follow their current location's time zone as they travel).
If you have a group of friends who like to stay in touch with regular group calls, and all/most of them are digital nomads who change their time zone of residence multiple times per year, you probably don't want the sticky paradigm.
This works for immutably recording the current time into a log, yes.
For much else (e.g. a timer still-to-come that should go off in “1000 days”), leap seconds break this.
You could store such time using TAI as the timezone (TAI is UTC without the leap seconds), if RDBMSes actually persisted the timezone. But they don’t. They’ll just convert back to UTC at point of write.
I have a feeling that most people who really need to solve this problem end up using a (pos, len) column pair where `pos` is the current UTC time when the future-event was registered, and `len` is an interval representing how far away it is in monotonic time — either as a difference of POSIX timestamps at time of evaluation, or as a SQL INTERVAL, etc.
One way to manage is to store the datetimes with a timezone identifier and the offset, and when you load a new tzdb, go through and validate that the calculated offset matches the stored offset... for those events where they don't match, you have an exciting challenge of figuring out if the event should stay with the time zone or stay with the offset; both answers may be right ... ideally you inform the user(s) about what you've done and allow them to fix things software has messed up.
It has the zone offset and so is completely unambiguous and invariable.
Converting Instant+ZoneId into a ZonedDateTime can vary according to the zone rules. Vice versa does not.Relational databases have exactly 1 type that corresponds to modern data-handling practices: timestamp with time zone, that stores a timestamp. There is no good way to store any other modern type, and the 1980s practices on time handling weren't actually very good.
Postgres can only store 1) unzoned local time, or 2) UTC, with optional conversion on read/write.
- Those with safety training for this class of foot-pointed firearms, who know how dumb shit looks like and that they should not do it;
- Those with officer training who are able to recognize the higher-level categories, and take principled approach to safety - e.g. recognizing that "date", "timestamp, "duration", "time of day", "time of week", etc. are different concepts and should not be mixed.
Adding months isn’t well-defined anyway, even when using date, for days of month > 28. I think it’s a mistake that systems generically allow such a computation (as opposed to application code implementing domain-specific business rules).
| Because the length of a month or year changes from one month or year to the next, ambiguities can arise when shifting a date by months and/or years. For example, what is the date one year after 2024-02-29? Is it 2025-02-28 or 2025-03-01? Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve this ambiguity, so the "ceiling" and "floor" modifiers (14 and 15) are available to let the programmer decide. If the next modifier after a time shift is "ceiling", then any ambiguity in the date is resolved by choosing the later date. The "floor" modifier resolves ambiguities by resolving to the last day of the previous month. The default behavior is "ceiling".
Don’t be a monkey.
Granted, mariadb has weird time things too... but thats the tip of the iceberg between autoincrement with vacuum, vacuum in general, and that thread from the other day about bad migrations/version upgrades...
When UTC offsets roundtrip you can do all your date math as if everything was in UTC, but still not lose information from the user about what time they thought an event occurred at or might next occur at.
For example, if your system is supposed to remind a user to do something, such as take a pill, and the user changes time zones while travelling, you may want the reminder to occur at 9:00 local time wherever they currently are. In that case, storing the original time zone together with the event may not be necessary, because the relevant time zone is the user's current one.
Everything depends on the context. For me, however, there is no doubt about one thing: I store timestamps in UTC. Whether I also store a time zone, and where I store it, depends on the system and its business requirements.
Postgres doesn't a native type that supports UTC offsets, but some other databases do and it is extremely useful. At this point in my career, I would never choose UTC storage over UTC Offset storage.
That is wrong. Postgres changes the timestamp to timestamptz very quietly in the background. Wether it uses the time zone of the session for this. If it is true or false depends on the TimeZone setting. This is more bad than "always false". In production with UTC it works. On a laptop of a California developer it does not work.
Just tested:
If you can, use Postgres 16. The double AT TIME ZONE 'UTC' thing from the text is not needed there. There you have date_add with a time zone as the third argument:date_add(b.month_start, interval '1 month', 'UTC')
This adds the month in UTC and it stays a timestamptz.
Naming.
Timestamps.
No, the original joke, which GP is referring to, is “There are only two hard things in computer science. Naming things and cache invalidation.”
It predates 1999 and the Y2K bug by a fair amount. I first saw it on Usenet in the early 90s, around 1994 I think.
More like
It only started causing widespread issues with the rise of cross-region internet SaaS. Database systems, language runtimes, and OS APIs are keeping the default behavior for backwards compatibility.
So in practice, while it was kinda useful to be locale/region dependant for some users it's probably been more trouble in the long run to be overly helpful.
C# was released after y2k, so they don't have the excuse.
Also, You're missing the biggest sin here however, locale specific time is OK, automatically allowing conversions/comparisons to points in time types without specifying timezones has in principle never caused anything but grief.
Wow, thank you for testing it. I did test it on my own laptop (Seattle).
> date_add(b.month_start, interval '1 month', 'UTC')
I didn't know this. Thank you.
Unfortunately this is just as misleading.
It's correct that `timestamp` doesn't contain timezone information, but *neither does `timestamptz`. All `timestamptz` is is UNIX-style Epoch timestamp, that is an absolute point in time in UTC. It says nothing about the timezone because there are no time zones in that context.
The confusion arises because when you insert into a field you can insert "19:00 on the 29th March 2025 in London", but this is simply converted to UTC at insert time and the timezone is lost.
If anything, `timestamp` does represent a time in the "real world" and `timestamptz` represents a time in the universe.
If time zones are important to you, you need to record the time zone in a separate field. That is all. Where you store this depends on context, of course. You could store the user's current time zone in a profile and render all times in that time zone (psql and other clients do this automatically based on the OS time zone btw). Or you could store the time zone with each record if that makes sense, ie. you want to know what the wall clock time was for the user at the time the record was taken.
That is also why calendar math and instant math disagree. interval '1 day' on a timestamptz is 24 hours, so a 09:00 local appointment drifts across DST. The usual fix is to strip to timestamp in the civil zone, add the calendar interval, then cast back. The double AT TIME ZONE 'UTC' in the post is that pattern with UTC as the civil zone, which only works if the civil zone really is UTC.
What I have settled on: store events as timestamptz, force TimeZone=UTC on every connection (app, migrations, replicas, psql), and convert to a named zone only at the edge. timestamp without time zone is fine for things that are not instants (a store's opening hours, a birthday) and a footgun for anything that is.
Only use timestamp (timestamp without time zone) if you really need local/plain datetime.
(Ideally, timestamptz would be called timestamp/instant, and timestamp would be called datetime.)
- Are you storing past events? Just store them as UTC. Maybe you use timestamptz to do it for you, but it's actually a more obtuse interface for that than timesstamp
- Are you storing future UTC times? Great, just use UTC, see above
- Are you storing future human times? Then timestamptz is actively harmful because it eagerly converts to UTC so even if you get an updated tzdb in time for when the event comes due, you don't know what happened at write time so now your datetime is ambiguous. It's less broken to use a plain timestamp + string timezone column (if you need to sort by it, maybe a denormalized _utc column too, with the understanding that you'll need to regenerate it or accept slight off-by-one errors when you update the tzdb, but at least you can do this when you know what the input value was, unlike with timestamptz)
Because of this overfitting OP lands on exactly the wrong advice. Unfortunately the quoted line is "Even Postgres Wiki says: Don't use timestamp without time zone" but wiki says "Don't use timestamp (without time zone) *to store UTC times*".
https://wiki.postgresql.org/wiki/Don't_Do_This#Don't_use_tim...
> Don't use the timestamp type to store timestamps, use timestamptz (also known as timestamp with time zone) instead.
> Why not?
> timestamptz records a single moment in time. Despite what the name says it doesn't store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it'll store the point in time that value describes. By default it will display times in your current timezone, but you can use at time zone to display it in other time zones.
> Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.
> timestamp (also known as timestamp without time zone) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don't know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.
> So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.
The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.
> Backpatching a behavioral change like this seems awfully scary. For the moment I'm just contemplating what we could potentially change in master. So far I don't like any of the choices :-(
[0] https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-...
UI time elements have to be presented in the user's TZ. In the DB one should store timestamptz in UTC for all things, and maybe also timestamptz in non-UTC TZs for user input (e.g., in a calendaring app).
- https://bookofrevenue.com/blog/
The only purpose of the naive timestamp is to defer a necessary step of converting a sort of nominal prototype or template to a real moment on the timeline. Until you pin them down, they don't really represent moments.
It's like having a NULL in the timezone slot. I think it should mean "unknown, anything goes on a per-value basis!", rather than treating it like some polymorphic type that gets magically parameterized at runtime.
IMHO, it is a mistake of the standard and PostgreSQL authors to try to enable such sloppy thinking by users and applications. Ordering relations shouldn't even be implemented on naive timestamps. It should be a type error, not a trigger for implicit coercion.
Perhaps it should even be a domain over text or some composite type that represents the partially populated time info. Require explicit mutation to populate the missing bits and allow conversion to a well-defined moment.
I think a sane application should only use the timezone-aware timestamp for storage, and explicitly manage its own "timestamp templates" and conversions before trying to do comparisons on the timeline.
Edit to add: I think you can say the same about timeline versus some timestamp-with-timezone strings. Make it more explicit that the ordered type is normalized moments. Make sure there is a normalizable external representation like ISO timestamps.
Make it clear that other representations are not stable. E.g. any legal timezone that could have its definitions change over time is not a stable concept to use in a representation of a moment. It is also effectively naive unless it includes another version parameter to state which version of the legal definition is intended.
>AT TIME ZONE 'UTC' converts the data type from timestamptz to timestamp
surprising and a foot gun. Asking for at a timezone stripping the timezone makes no sense to me. Having `AT TIME ZONE 'UTC'` produce a timestamptz at +0:00 seems not insane.
This saves a __lot__ of headaches, of which timestamp vs timestamptz is the tip of the iceberg.
Er… just write '2026-02-28 16:00:00-08Z'::timestamptz.