4

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 |
+------------------------------------+-----------------------------+
Damon Malkiewicz
  • 393
  • 5
  • 15
  • 1
    A `datetime2` of the same precision will be smaller in storage terms. If you aren't planning to ever store anything other than UTC, then use `datetime2`. – Thom A Feb 12 '21 at 17:10
  • Datetime offset will allow comparison between different offsets of the same time. e.g. `CAST('2021-02-12 15:48:11.0677934 -01:00' AS datetimeoffset) = CAST('2021-02-12 16:48:11.0677934 +00:00' AS datetimeoffset)` – Dan Guzman Feb 12 '21 at 17:12

1 Answers1

10

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

SELECT 'equal'
WHERE
    CAST('2021-02-12 15:48:11.0677934 -01:00' AS datetimeoffset) = CAST('2021-02-12 16: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 datetime needs 8 bytes for precision 5 or greater, 7 bytes for precision 3-4, and 6 bytes for precision 2 or less.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71