Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
From the creators of devRant, Pipeless lets you power real-time personalized recommendations and activity feeds using a simple APILearn More
Search - "datetimetz"
My preferred stack is Rails/NginX/Postgres, or Node using the same.
I have a fair amount of material for this week's rant, but in my stack's defense, the quantity is primarily because I've been using it for so long, and I'm apparently a talented breaker. I may share other stories if the motivation arises.
However, today I ran into something definitely deserving of calling out.
The default datatype for a Date+Time column in Postgres is `datetime` which means "date+time without timezone". (while `datetimetz` instead stores the timezone).
Apparently when comparing a datetime with a datetimetz, Postgres doesn't compute the timezone difference correctly, leading to some very unexpected and confusing query results.
Today, I had a record that was both pending (expires_at > now) and expired (expires_at <= now), where now is a DateTime (with tz) literal from Rails. After half an hour's frustrated delving and baffled expressions at query results, I finally figured out that the database's math was incorrect when comparing UTC (+0) and PST (-7).
This during a semi-high-priority bugfix that's blocking for a coworker.
While Time and all of its nuances are honestly extremely difficult to handle correctly, I didn't expect Postgres to get this relatively simple part wrong.
Shame on you, Postgres.
I expected better.3