Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

datetimeoffset vs datetime2 for UTC on SQL Server

Are there any benefits to storing a UTC timestamp in a datetimeoffset field vs datetime2? It seems they're essentially the same.

+------------------------------------+-----------------------------+
| datetimeoffset                     | datetime2                   |
|------------------------------------+-----------------------------|
| 2021-02-12 16:48:11.0677934 +00:00 | 2021-02-12 16:48:11.0677934 |
+------------------------------------+-----------------------------+
like image 605
Damon Malkiewicz Avatar asked Mar 27 '26 19:03

Damon Malkiewicz


1 Answers

The datetimeoffset data type will allow comparison between different offsets of the same time. e.g.:

SELECT 'equal'
WHERE
    CAST('2021-02-12T15:48:11.0677934-01:00' AS datetimeoffset) = CAST('2021-02-12T16:48:11.0677934+00:00' AS datetimeoffset).

If you are storing only UTC values (where the offset is always zero), you can save storage space with datetime2. datetimeoffset requires 10 bytes of storage whereas datetime2 needs 8 bytes for precision 5 or greater, 7 bytes for precision 3-4, and 6 bytes for precision 2 or less.

like image 103
Dan Guzman Avatar answered Mar 29 '26 10:03

Dan Guzman



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!