Contents
How do you handle time zones?
Time Zone Strategies
- Store all timestamps using UTC. As you might expect, it would be a lot easier if we didn’t have to deal with time offsets at all.
- Always use a library to perform date and time math. Performing any kind of math or comparison on a timestamp by yourself is risky.
- Use a standard time offset.
How does SQL store time zones?
Storing Time Zones on an SQL Server
- Simply store the IANA identifier as a string along with each location.
- Store all IANA identifiers in a separate table and use a foreign key to link to it.
Should I store TimeZone in database?
Databases will convert any datetime into a UTC epoch to store internally. If that’s the case, it’s recommended to convert all dates to UTC before storing. Don’t use local timezone.
How to manage multiple time zones in data mart?
To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following: You say… The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.
Can a data mart be an OLTP database?
As you’re building a data mart, and not an OLTP database, the generation of the Local and Utc times should be performed in your ETL, NOT in any client side applications for the following reasons (apart from localisation of the UTC time to the report reader’s perspective):
What should be included in a time dimension?
A time dimension makes a little more sense but I’m having hard time with the date dimension. A general design approach for a date dimension usually includes properties such as day name, day of week, month name, etc.
Why are there two columns for time zones?
Having the two separate column types (Local and UTC), allows for side-by-side comparison across geographic distance.