2

I'm looking at a Data Warehouse which collections data from systems across the world. The Data Warehouse uses UTC for time but only stores the UTC offset and not the timezone. I have noticed that the datetime data from e.g. America has been inserted with an offset of +0000 which suggests it has been stored as UTC instead of local time. As far as I can see in the code that loads the data there is no conversion to convert the datetime from local time to UTC.

I'm fairly new to DWH data modelling but from what I have read I would have thought that we should either store the data with the converted UTC offset, store the datetime with timezone included or store UTC and local datetime as this could have an impact on reporting. None of the reporting teams have raised an issue with this at the moment so should it be a concern or is it acceptable to save everything as UTC when the data is coming in from different countries across the world?

Thanks

1
  • If your dates represent a time relative to the user's clock (for example a scheduling application) you probably want local time not UTC. But unlikely in a DW. Feb 27, 2023 at 14:35

1 Answer 1

3

As a general rule it's always better to store DATETIMEs in UTC, and then have them converted to the appropriate timezone in the display layer (be that an application, report, etc.). This can solve problems where you deal with multiple timezones, and reduce issues surrounding daylight savings time as well.

It's also a good idea to name DATETIME columns with a UTC suffix to make it clear to everyone that might use them that this is a UTC DATETIME (OrderDateTimeUTC, LastEditDateTimeUTC).

If you have a DATETIME which does not also include timezone information there's literally no way to determine that it is in fact UTC, or any other timezone. It just wasn't part of the data.

SELECT CAST(CURRENT_TIMESTAMP AT TIME ZONE 'Canada Central Standard Time' AS DATETIMEOFFSET) AS CurrentSystemTimeWithTimeZone, 
       CAST(CURRENT_TIMESTAMP AS DATETIMEOFFSET) AS CurrentSystemTimeWithoutTimeZone, 
       CAST(GETUTCDATE() AT TIME ZONE 'UTC' AS DATETIMEOFFSET) AS CurrentSystemTimeWithTimeZone, 
       CAST(GETUTCDATE() AS DATETIMEOFFSET) AS CurrentUTCTimeWithoutTimeZone 
CurrentSystemTimeWithTimeZone CurrentSystemTimeWithoutTimeZone CurrentSystemTimeWithTimeZone CurrentUTCTimeWithoutTimeZone
2023-02-24 11:07:41.4000000 -06:00 2023-02-24 11:07:41.4000000 +00:00 2023-02-24 17:07:41.4000000 +00:00 2023-02-24 17:07:41.4000000 +00:00

Where I used AT TIME ZONE the appropriate offset was included, where I did not, it wasn't. There's no way to tell CurrentSystemTimeWithoutTimeZone is not UTC.

You can go back through the code that's responsible for the data and determine what the DATETIME represents of course, but just looking at the database isn't going to be helpful.

2
  • Thanks. If I loaded data from an application in America/Los_Angeles -08:00 which does not record the UTC offset into a database that uses UTC what is the best approach to storing the data? Is it a timestamp column for the original date time and a column for the date time converted to UTC or would you just have one column for the date time converted to UTC or just store the original datetime (I think this would be incorrect). Feb 27, 2023 at 10:02
  • The most correct way? Find out for sure what timezone your data is, and convert it to UTC. This will mean you'll have to make changes to anything and everything that uses it, though. Alternatively, you could add a column that stores the UTC value and use it when you need utc. Feb 27, 2023 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.