‹ BackHN Continuity

Thread

Footguns with Postgres “at time zone 'UTC'”

169 points · 114 comments · birdculture

  1. heurekamala · · focus · HN ↗
    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:

    date_add(b.month_start, interval '1 month', 'UTC')

    This adds the month in UTC and it stays a timestamptz.

    1. whizzter · · focus · HN ↗
      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.
      1. reactordev · · focus · HN ↗
        The two most difficult things in software engineering...

        Naming.

        Timestamps.

        1. kachnuv_ocasek · · focus · HN ↗
          And of by one errors
          1. matroxmemories · · focus · HN ↗
            You are off by one characters.
            1. ButlerianJihad · · focus · HN ↗
              In my freshman Pascal class, I quickly learned how to use “iff” [if and only if] in a sentence, and my T.A. loved that.
        2. tyre · · focus · HN ↗
          You must not have invalidated your cache since a third thing.
          1. reactordev · · focus · HN ↗
            Obviously there are more but the original joke circa 1999 was this. Made Y2K even more exciting.
            1. dleary · · focus · HN ↗
              > 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.

              1. cryptonector · · focus · HN ↗
                > There are only two hard things in computer science. Naming things and cache invalidation.

                More like

                  There are only two hard things in
  computer science. Naming, cache
  invalidation, and off-by-one bugs.
                1. fragmede · · focus · HN ↗
                  too, parallel processing!
                2. vladsanchez · · focus · HN ↗
                  AND... (drumroll) Containing AI Agents! LOL
        3. Dylan16807 · · focus · HN ↗
          Timestamps aren't hard. It's almost everything else with time that's hard.
          1. vincnetas · · focus · HN ↗
            For examples check : "Falsehoods programmers believe about time..."
        4. vincnetas · · focus · HN ↗
          Have we solved cache eviction?
      2. layer8 · · focus · HN ↗
        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.

        1. whizzter · · focus · HN ↗
          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.

        2. lmz · · focus · HN ↗
          It's not even global, is it? It's session scoped.
    2. tanin · · focus · HN ↗
      Author here

      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.

    3. anitil · · focus · HN ↗
      This is one of those moments that makes me wonder how anything ever works at all
    4. linasdev404 · · focus · HN ↗
      Adding a month is a footgun even without time zones. I was checking a spreadsheet against another implementation month by month and found that HyperFormula 3.4 returns Feb 28 for EDATE(Jan 31 2028, 1). Excel, Google Sheets and Numbers all return Feb 29, because 2028 is a leap year. Jan 31 + 1 month in a leap year is now a test case in anything I write that does month math.
      1. greenflux · · focus · HN ↗
        Thank you for pointing this out with HyperFormula! Appreciate you testing in various environments and sharing the results. I've confirmed the issue and submitted a PR with a fix that should be in the next release.
Open on Hacker News to reply ↗

Unofficial Hacker News client; not affiliated with Y Combinator.