Rendered at 16:19:30 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
tibbar 2 hours ago [-]
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).
spacesez-ai 55 minutes ago [-]
Does the same hole exist for range types? If a GiST exclusion constraint on tstzrange is built on the same comparison logic, a booking table could accept two reservations that overlap only inside the DST gap, and nobody would notice until two people show up for the same room. Curious whether you checked that path or only the btree family.
heurekamala 6 hours ago [-]
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:
This adds the month in UTC and it stays a timestamptz.
whizzter 4 hours ago [-]
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.
layer8 2 hours ago [-]
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.
whizzter 25 minutes ago [-]
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.
reactordev 3 hours ago [-]
The two most difficult things in software engineering...
Naming.
Timestamps.
tyre 3 hours ago [-]
You must not have invalidated your cache since a third thing.
reactordev 2 hours ago [-]
Obviously there are more but the original joke circa 1999 was this. Made Y2K even more exciting.
dleary 2 hours ago [-]
> but the original joke circa 1999 was this.
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.
kachnuv_ocasek 3 hours ago [-]
And of by one errors
matroxmemories 25 minutes ago [-]
You are off by one characters.
ulrikrasmussen 7 hours ago [-]
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, but the time zone is thrown away and not stored after the value has been parsed. This is different from e.g. `ZonedDateTime` in Java which will actually store the offset and therefore corresponds to a pair of (Instant, TimeZone).
masklinn 6 hours ago [-]
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)
marcosdumay 38 minutes ago [-]
Still, the GP is correct about the problems.
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.
rawling 4 hours ago [-]
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!)
pseidemann 3 hours ago [-]
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).
skrtskrt 2 hours ago [-]
What’s the difference between these two other than how the client would convert to display to a user?
pseidemann 50 minutes ago [-]
It's not only about display. If you store a user's appointment only as a UTC timestamp, you actually can't know the hour of the day (and the day itself to be precise) on which this appointment should happen, for a given calendar (probably the user's calendar, in a specific non-UTC timezone). You would have to guess by using the calendar's/user's timezone and compute some offset with UTC. But what if the user changes timezones or the timezone itself changes its value? Store without a timezone, and you know the exact hour and day the user intended. One is pointing to a day and hour in a calendar, the other is pointing at a point on the line of a linear timeline.
masklinn 3 hours ago [-]
It’s really not clear what you’re asking.
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.
rawling 2 hours ago [-]
In reply to
> 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.
paulddraper 3 hours ago [-]
A java.time.ZonedDateTime is not simply zoneid+date+time.
It has the zone offset and so is completely unambiguous and invariable.
private final LocalDateTime dateTime;
private final ZoneOffset offset;
private final ZoneId zone;
Converting Instant+ZoneId into a ZonedDateTime can vary when zone rules change. The inverse does not.
ulrikrasmussen 6 hours ago [-]
Ah, yes, you are right! Forgot about those two details
officialchicken 6 hours ago [-]
SET TIME ZONE 'UTC'; // or GMT, PST, etc.
crote 5 hours ago [-]
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.
> 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).
beybol 6 hours ago [-]
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.
wodenokoto 5 hours ago [-]
So how are you solving the example in the article where they join on timestamps? Read both tables from the DB into the application?
beybol 4 hours ago [-]
I read raw data (UTC timestamps) from the db and timezone for user profile and calculate it on the fly.
threatofrain 5 hours ago [-]
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.
GJim 6 hours ago [-]
This is the only way. To do otherwise smacks of poor programming practice and is very often a sign the coder doesn't habitually consider the world outside their own timezone.
Macha 50 minutes ago [-]
It only works backwards (though you can probably get away with it if your future dates are not too far forward). There are ~10 tzdb updates a year due to rule changes and if you bake a date on the other side of one to UTC too early then your app is going to have incorrect datetimes
ahoka 6 hours ago [-]
I've seen a product where they used UTC to store opening hours. They had to rewrite all dates via a script twice a year.
beybol 4 hours ago [-]
I don't know the exact use case, but it sounds weird. If UTC is the single source of truth, then when you display the data and want to change some business assumptions, you only need to change the application logic. The underlying data remains unchanged.
One scenario I can imagine is when you want to store the results of business calculations in the database. In that case, once the algorithm changes, you may also need to update the stored data. This may be necessary for performance reasons.
In all other cases, calculating the result on the fly solves the problem and does not require a database update when the business rules change.
GJim 6 hours ago [-]
Why would the dates need rewriting?
sokoloff 5 hours ago [-]
I suspect they meant more generally “the date&time field”, but a store in Boston that opens at 7:30 AM and closes at 7:30 PM Eastern time (ET) closes on different UTC date than it opens when ET is EST but opens and closes on the same date when ET is EDT, so it’s plausible that the dates actually needed to be updated.
Felk 7 hours ago [-]
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 ...
jokull 2 hours ago [-]
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*".
Macha 2 hours ago [-]
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 timestamp
- 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)
epgui 3 hours ago [-]
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.
nubinetwork 6 hours ago [-]
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...
codesnik 6 hours ago [-]
postgres is sane and consistent. Some things could seem weird, but those usually track to SQL standards, which are weird and dated, but at least consistency is still there. On the other hand, I never can be sure where mysql/mariadb decided to improve my life.
nubinetwork 6 hours ago [-]
Oh believe me, I dislike mariadb for other reasons, like the fact that it sucks at picking indexes for queries.
braiamp 4 hours ago [-]
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
ekejjedjndbd 4 hours ago [-]
If the UTC is derived why store it (unless just as a performance concern e.g. materialised view type of thing)
christina97 3 hours ago [-]
Presumably if the conversion logic changes. Eg user enters a future date in a place but the timezone in that place changes in the future.
globular-toast 6 hours ago [-]
> 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.
ForHackernews 7 hours ago [-]
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 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.
paulddraper 4 hours ago [-]
TLDR Always use timestamptz (timestamp with time zone).
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.)
walrus01 8 hours ago [-]
[flagged]
FooBarWidget 7 hours ago [-]
? 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.
walrus01 7 hours ago [-]
While that is true (the LLMs surely had to learn it from pre-existing content), I think it's been so thoroughly overused and turned into a cliche that the term has been ruined for the foreseeable future.
wongarsu 6 hours ago [-]
Looking forward to the blog post "Term footgun considered harmful"
cloudie78 6 hours ago [-]
[flagged]
cowboylowrez 4 hours ago [-]
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!
estetlinus 7 hours ago [-]
Makes me wonder what kind of footgun we are talking about here — is it classic, smoking, standard or vanilla?
officialchicken 6 hours ago [-]
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.
TeMPOraL 5 hours ago [-]
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.
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).
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.
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.
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.
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, but the time zone is thrown away and not stored after the value has been parsed. This is different from e.g. `ZonedDateTime` in Java which will actually store the offset and therefore corresponds to a pair of (Instant, TimeZone).
- 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)
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.
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.
It has the zone offset and so is completely unambiguous and invariable.
Converting Instant+ZoneId into a ZonedDateTime can vary when zone rules change. The inverse does not.Postgres can only store 1) unzoned local time, or 2) UTC, with optional conversion on read/write.
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).
One scenario I can imagine is when you want to store the results of business calculations in the database. In that case, once the algorithm changes, you may also need to update the stored data. This may be necessary for performance reasons.
In all other cases, calculating the result on the fly solves the problem and does not require a database update when the business rules change.
timestamp to timestamptz: AS ZONED AT TIME ZONE ...
timestamptz to timestamp: AS LOCAL AT TIME ZONE ...
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*".
- 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 timestamp
- 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)
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...
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.
https://oneuptime.com/blog/post/2026-01-25-postgresql-timezo...
- https://bookofrevenue.com/blog/
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.)
- 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.