[Waterluvian]

Once I have a datetime safely in my database as UTC, I don’t need to record the original timezone, right?

(assuming I’m not planning to need to know the history of the database entry)

[d_watt]

It depends on the implementation and definition of “datetime” in whatever you’re working with. Taking Postgres as an example:

date: the concept of a calendar date (Your birthday)

timestamp: the concept of of a calendar datetime (You should get a new years kiss at 2022-01-01 00:00)

timestamptz: the concept of a precise moment in history (This comment was written at xxx time utc)

You can design a system where timestamps/datetimes are considered to be precise moments in time, utc, but that’s a matter of the impmentation you’re dealing with. Again, postgres does not assume timestamps as being in UTC (which has messed me up on more than one occasion).

[wiredfool]

Dates from times are a minefield.

I just had a long-standing bug come to the fore because I shifted a view to a materialized view, and embedded deep down in it was a date cast from a time stamp with tome zone.

When running as a view, everything was done in the user's time zone (because tits set per connection). When it’s a materialized view, it's he refresher's time zone.

This led to some inconsistencies, as the server is properly but inconveniently in UTC.

[0x0]

Not if you are working with timestamps in the future, no. Someone may want a meeting to happen when the wall clock displays 4pm at some day in the future, no matter what unknown time zone rule changes will be introduced in the meantime. In those cases you will need to store the local time and a timezone identifier (or geographical identifier).

[rileymat2]

It depends on whether or not the local time is meaningful or not.

Say you are recording logins from a user. A suspicious login may be outside of work hours in local time. Without knowing the local time, you cannot apply this rule.