Does DATETIME type include timezone information?

Question

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?

Misshape

The only data type that stores timezone information is datetimeoffset. – Vampire

...and datetimeoffset is not DST-aware. But if you trust the source, no, you don't have to tell SQL Server it's UTC. – Lily

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 – Continual

Great - I was concerned tripping over myself if the DB is trying to be too clever basically :) – Misshape

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. – Lily

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 – Continual

@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 :) – Misshape

@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) – Continual

@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. – Continual

@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. – Lily

@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 – Stefanysteffane

@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 – Continual

@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. – Lily

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. – Misshape

@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 – Continual

@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. – Continual

@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}. – Lily

@PanagiotisKanavos (Others don't agree, 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.) – Lily

Answer 1

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.

Misshape

"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. – Jacobs

Answer 2

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.

Jacobs

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. – Detestation