9

I want to use UTC timestamps exclusively in an MSSQL database. When writing the current timestamp I can use system methods like GETDATEUTC and we have agreed with suppliers that timestamps sent from external sources will also be provided as implicit UTC - but when writing these to the DB do I need to tell MSSQL that it is a UTC date somehow? Or is it my responsibility to know what timezone I'm using?

Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • 5
    The only data type that stores timezone information is `datetimeoffset`. – Thom A Jan 12 '22 at 15:44
  • 2
    ...and `datetimeoffset` is not DST-aware. But if you trust the source, no, you don't have to tell SQL Server it's UTC. – Aaron Bertrand Jan 12 '22 at 15:45
  • If you care about timezones use `datetimeoffset`. Don't use `datetime`, that's an obsolete legacy type. Use `datetime2` instead. Neither `datetime` nor `datetime2` holds *any* kind of timezone information though – Panagiotis Kanavos Jan 12 '22 at 15:45
  • Great - I was concerned tripping over myself if the DB is trying to be too clever basically :) – Mr. Boy Jan 12 '22 at 15:45
  • Best practice IMHO would be to set the server to UTC. Then you don't have to worry about things like `getdate()` slipping past code reviews. – Aaron Bertrand Jan 12 '22 at 15:46
  • Use `datetimeoffset` if you want roundtripping. Also if you care about having the correct type. Storing UTC isn't enough - timezone offsets change quite frequently – Panagiotis Kanavos Jan 12 '22 at 15:46
  • 1
    @AaronBertrand true but I don't control the server and I don't want to have the headache where someone didn't set the server right. I'm in the UK so half the year local time IS UTC, which can make problems harder to notice :) – Mr. Boy Jan 12 '22 at 15:47
  • @AaronBertrand (looking for a Pier-Luigi Colina gif - can't post it here anyway) that's as risky as assuming Latin1. If you have data that could come from multiple timezones for *any* reason, you can't assume UTC. Russia changed DST rules twice in 5 years. Egypt changed its DST rules with a few weeks notice. Seriously, the only useful solution is to use IANA timezone names, something SQL Server can't do. (I was working for an online ticket agency when all those DST rules changed) – Panagiotis Kanavos Jan 12 '22 at 15:48
  • @Mr.Boy the real solution would be to store the time including the IANA timezone name, eg `Europe/London` and convert as needed. Airlines publish schedules using both offsets and IANA tz names but while offsets can change, the TZ name will never change. Unfortunately, SQL Server doesn't support this - yet. The `AT TIME ZONE` clause only uses Windows timezone names which are ... non-standard. – Panagiotis Kanavos Jan 12 '22 at 15:50
  • @Panagiotis But you have to trust the source in either case: if you ask for UTC and trust that it’s UTC, that they don’t send you a local time, or if you ask for local time and trust that it’s local time, that they don’t send you UTC or a different “local” time zone or forgot to adjust for their own DST. – Aaron Bertrand Jan 12 '22 at 15:54
  • @Mr.Boy the fun one is when the database uses `GETUTCDATE` but the webserver uses the local time - that's a farce to dig through – Andrew Corrigan Jan 12 '22 at 15:55
  • @AaronBertrand when an airline tells me a flight departs at 05:00 America/New_York or Europe/Moscow there's no ambiguity and nothing to trust. Even if the DST rules change the flight will still depart at 5 am local New York time – Panagiotis Kanavos Jan 12 '22 at 15:56
  • @PanagiotisKanavos But it sounds like these third parties are _just sending a datetime value_. You can ask them to add timezone information or you can ask them to make sure they always send Moscow time or you can ask them to send UTC. They might _still_ send the thing you didn't ask for (including UTC incorrectly labeled with a local timezone). So I don't agree that asking them to add the timezone name to the information is reliable, even if they _can_ change it, which sometimes they can't/won't. So in all cases there is some trust involved. – Aaron Bertrand Jan 12 '22 at 15:58
  • Indeed. We are talking datetimes sent from hardware units using a specific protocol, using their local clock. All we can do is agree we will _use_ the supplied data as UTC. If it isn't, their fault. – Mr. Boy Jan 12 '22 at 16:00
  • @AaronBertrand airlines post the airport local time so you know the IANA timezone. In fact, they also publish the IANA tz name *and* the ISO8601 offset but the safe value is local+tz name. This way, if they get it wrong *they* get to refund the customer, not the agent. If I stored UTC and made a mistake, I'd have to pay the refund. With eg Egypt's change, I'd have to go and change all `datetimeoffset` or UTC values. By storing the local time *and* airport/tz name, I didn't have to change anything – Panagiotis Kanavos Jan 12 '22 at 16:00
  • @Mr.Boy if you already know their timezone you could store it along with the local time. You can use NodaTime to correctly handle timezones. – Panagiotis Kanavos Jan 12 '22 at 16:03
  • 1
    @PanagiotisKanavos You keep talking about airlines; I think we're talking about something with less-well-defined international standards. I still insist that if you're taking data from another party there is some trust you have to have in what they're sending you regardless of whether or not you like UTC. I prefer UTC simply because everyone knows what it is and because it's easier to change `from UTC to {some timezone that may or may not support DST}` than it is to change `from {some timezone that may or may not support DST} to {some other timezone that may or may not support DST}`. – Aaron Bertrand Jan 12 '22 at 16:03
  • @PanagiotisKanavos ([Others don't agree](https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/), and that's ok. No solution is perfect, and we each must make our own qualitative and subjective decisions given our individual environments/situations. This question started with `I want to use UTC timestamps exclusively...` so that further reinforces why I lean toward UTC.) – Aaron Bertrand Jan 12 '22 at 16:11

2 Answers2

8

To provide an answer, in short Neither Datetime nor Datetime2 encodes timezone information, only the raw date/time data specified. It is up to the developers/DBAs/users to agree what the data means.

Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • *"It is up to the developers/DBAs/users to agree what the data means."* note that a `DATETIME` is _independent of a time zone_, so it's not like they're agreeing on a time zone. It is simply an abstract representation of instant in time; that instant represents different times/dates in different time zones, but it is the same instant in time in all of them, and thus the same `DATETIME`. You can convert it to a specific time zone using [`AT TIME ZONE`](https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16). – Ryan M Apr 27 '23 at 20:16
0

No, a DATETIME is independent of any time zone.

It is simply an abstract representation of instant in time; that instant represents different times/dates in different time zones, but it is the same instant in time in all of them, and thus the same DATETIME.

You can convert it to a specific time zone using AT TIME ZONE. That will give you a datetimeoffset representing the time and date in a particular time zone that corresponds to that instant in time.

Ryan M
  • 18,333
  • 31
  • 67
  • 74
  • It is worth noting that AT_TIME_ZONE isn't deterministic because it relies on timezone info provided by the OS. I still feel that agreeing on storing only UTC based datetimes is preferred and then let the application layer work out what timezone that UTC datetime needs to be displayed in. – rene Apr 28 '23 at 09:58