> On Apr 10, 2020, at 6:10 PM, Jeremy Morton <ad...@game-point.net> wrote: > > Neil wrote: >>> On Apr 10, 2020, at 8:19 AM, Jeremy Morton <ad...@game-point.net> 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. >> 1. Not sure I understand. I’ve never used datetimeoffset so please bear with >> me. How does storing a time zone with the date time “make it impossible for >> developers to make the mistake….” > > At just about every development shop I've worked for, I've seen developers > use methods to get a local DateTime - both in the DB and in the code - such > as DateTime.Now, and throw it at a DateTime field. Heck, even I've > occasionally forgotten to use .UtcNow. With DateTimeOffset.Now, you can't go > wrong. You get the UTC time, and the offset. I've taken to using it 100% of > the time. It’s just really handy. >
In PostgreSQL there are two types; timestamp and timestamptz. If you use timestamptz then all time stamps coming into the database with time zones will be converted to and stored in UTC in the database and all times coming out of the database will have the local time zone of the server unless otherwise requested. Not sure how that is error prone. Maybe you are working around a problem that does not exist in PostgreSQL. If you use timestamp type (not timestamptz) then all input output time zone conversions are ignored (time zone is truncated) and sure problems can occur. That is why there is very little use of the timestamp type. Neil https:://www.fairwindsoft.com
