Discussion spreads to Mastodon and social platforms
4 Yesterday 4:22 AM · 22h ago · 2 posts · 1 comment · 2 sources · development 4 of 4
The post gains further reach as it's shared across Mastodon and other platforms, with commentary on timezone handling and database best practices.
tanin47 Blog post author
The whole story articlespostscomments the bright band is this development · numbered dots are the others · click one to jump
What people said 17 voices · best of 18 · verbatim
-
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…
-
Yes. I know. I understand this. An Instant is measured against a reference point that has a known relationship to the world's time zones, so you can translate the instant's value to its representation in any time zone. A LocalDateTime has no reference point, so it has no relationship to time zones at all. (It's basically the same as timestamp with…
-
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…
-
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…
-
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…
-
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…
-
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…
-
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…
-
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 ...
-
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.
-
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.
-
> 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).
-
I’m having a really hard time understanding this. I don’t work on Postgres so cut me some slack.Apparently both timestamp and timestamptz saves 8 bit integer that represents a datetime in a similar fashion as Unix epoch.Neither of them stores a timezone or an offset.So what are they?
-
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.
-
Author hereWow, 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.
-
> '2026-02-28 16:00:00-08'::timestamptz AT TIME ZONE 'UTC'Er… just write '2026-02-28 16:00:00-08Z'::timestamptz.
-
This is one of those moments that makes me wonder how anything ever works at all
All 4 developments of Postgres AT TIME ZONE 'UTC' converts data type unexpectedly →
RedditHacker NewsMastodonNewswires