On 4/10/20 3:19 PM, Jeremy Morton wrote:
> Oh well. Guess I keep using SQL Server then. datetimeoffset makes it
> impossible for developers to make the mistake of forgetting to use UTC
> instead of local datetime, and for that reason alone it makes it
> invaluable in my opinion. It should be used universally instead of
> datetime.
I think that the timestamptz type already helps out a lot with that
since it accepts input strings with a time zone offest (e.g. '2020-04-10
17:19:39+02') and converts it to UTC after parsing the timestamp. In
fact I would argue that it does so with fewer pitfalls than the
datetimeoffset type since with timestamptz everything you read will have
the same time zone while when you read a datetimeoffset column you will
get the time zone used by the application which inserted it originally,
and if e.g. one of the application servers have a different time zone
(let's say the sysadmin forgot to set it to UTC and it runs in local
time) you will get a mix which will make bugs hard to spot.
I am not saying there isn't a use case for something like
datetimeoffset, I think that there is. For example in some kind of
calendar or scheduling application. But as a generic type for storing
points in time we already have timestamptz which is easy to use and
handles most of the common use cases, e.g. storing when an event happened.
Andreas