> I think best practice is to have PostgreSQL run in UTC and always store a time zone, that way you're always aware of time zone info and aren't restricted in the future.
- you're time zone aware but you have to deal with time zones
- you're time zone naive but you can't do anything you need time zones for, like converting between time zones or using time zones (and DST) in time zone math
I think the recommendation is "figure out how to get time zone info and store it, otherwise you foreclose time zone functionality and managing DST", but yeah up to you if you're willing to accept that risk.
- It doesn't know I was using EST, so it's off by 5 hours now.
- If I ever want to do math across the DST switch, I can't, because my original tzinfo was lost.
It's not wholly unreasonable to want to avoid time zones, but personally I think you should always be time zone aware and build it into your app in a reasonable way, though I recognize that's easier said than done. Mostly I guess my feeling is "get over it" though haha.
SELECT ('2023-03-12 03:00:00 America/New_York'::TIMESTAMP WITH TIME ZONE - '30m'::INTERVAL) AT TIME ZONE 'America/New_York'; -- Do some math over US DST switch
You need your initial datetime to have a time zone if you want to do math that's aware of time zones. And sadly if you don't store time zones (all times are "local"!) then you can't get that information back.
I think best practice is to have PostgreSQL run in UTC and always store a time zone, that way you're always aware of time zone info and aren't restricted in the future.
The problem here is actually knowing what time zone to use. You can get quagmired pretty quickly in like, do we surface this to users, do we use browser location, is there an address/location we can key off of, how do we update it as they move, blah blah. I understand the appeal of being time zone naive. But it's not cut and dry IME.