18
Root
2y

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.

Comments
  • 2
    Fuck timezones...
  • 2
    Thanks for yhus informative rant Root. I bet you're gla you found the reason behind the shenanigans. I wish you a better DateTimeZ.

    Alos what version of postgres are you using?
  • 1
    @bioDan @AlexDeLarge
    Postgresql v10.3 via brew on OSX

    No, I haven't reported it because I haven't ruled out user error or the manure of a codebase I'm hacking on...

    I'll look more into it if/when I have free time.
Add Comment