perry wrote: datetime field, where date field is time zone independent and datetime field is always stored as UTC.
... convert UTC based on time zone setting from
Having dealt with time-based data across time zones and daylight savings changes in a past life, even using UTC is not always enough. At least not on its own - you always need some other external bit of information - as you suggest - like a timezone setting stored somewhere else.
That's why I was quite pleased to see the introduction of the "date/time offset" data type - which basically stores the UTC time, along with the time offset at the time of the transaction. As a troubleshooting tool, it can be really helpful, because you can see at a glance from the one data field if transactions have been coming from multiple time zones.
And if you have transactions happening between 2:00am and 3:00am on the day you're switching out of daylight savings - it can save a lot of head scratching if you have e.g. +6:00 and +7:00 on the timestamps.
It can also tell you if you have a system with the wrong time zone.
For new code, it's really easy to just use it instead of the traditional date/time data types, and write all the code with that in mind.
But for existing code, it is a right pain to change, because you have to assess
every place where a date/time is used to make sure you have the semantics right. Mostly it's easy, but it is nonetheless a lot of tedious work.
You would also have to write some fancy SQL to update existing values to the right time zone(s).