Is there any sense to store a UTC date in DateTimeOffset Column instead of DateTime(2)?

c# entity-framework-core sql-server

Question

For me it should be Localtime stored DateTimeOffset OR UTC in DateTime(2). Not UTC in DateTimeOffset. But I might be wrong.

Is there any logical reason to store UTC in a DateTimeOffSet DB.Column? If so, it will always look like " +00:00" Makes no sense. Does a DateTimeOffset Column consume more than 8 bytes?

1
0
4/5/2020 3:50:30 PM

Accepted Answer

UTC being stored in a datetimeoffset makes sense, provided you're going to be storing other timezones too. If every time you're going to store is UTC, then you might as well use datetime2 (or possibly smalldatetime depending on your accuracy requirements).

Even if all your data is for one country, but that country observes DST, then it might be worth using datetimeoffset. then times in the summer might be UTC +01:00 and in the winter UTC +00:00. But that depends if knowing if DST is in effect is important (it might not be).

Note, however that datetime2 is a smaller datasize than a datetimeoffset, so you do need to take that in considering when working with much larger result sets. If you look at Date and Time data types you'll note that datetime2 uses 6-8 bytes however, datetimeoffset uses 8-10 bytes (so the smallest size datetimeoffset is as large as the largest datetime2).

1
4/5/2020 4:14:17 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow