[camgunz]

Not an expert but I think the issue is datetime math. Ex:

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.

[thermin]

I absolutely agree, but timestampz as a column type is of little help here, because no actual timezone info is being persisted, ever.

> 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.

[camgunz]

Yeah the tradeoffs here are:

- 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.

[mrrubberburner]

Timezonetz is explicitly designed for simplified arithmetic. By storing local values that have been converted to the utc constant, you get around the problem entirely. I think there must be some fundamental misunderstanding in these threads that information is somehow being lost by storing in utc when that isn’t at all true.

[camgunz]

Eh, not really. If I try and store a local (time zone naive) datetime from EST, it will be "converted" (read: interpreted as) UTC. Well that's bad, because:

- 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.