Leaving orbit is independent of the number of orbits. At any given time, multiple time zones can have the same offset, and due to daylight saving time regulations, some time zones may alternate between two different offsets throughout the year.
Question:
My task involves developing an
SSIS Package
that extracts data from a production database and loads it into a
reports server
/
data warehouse
database. Unlike the other databases hosted on my reports server, this database operates on UTC time zone. I’m unsure whether to perform time zone conversion during data import using SSIS or maintain the original time zone and manage it through queries based on the
time zone
information. Additionally, is there any possibility to modify the transferred data using a
reason not to convert
approach?
Solution 1:
Keeping the times in UTC instead of converting the timezone has its advantages. UTC is always accurate and can easily be converted to any given timezone, even when timezones shift or Daylight
Savings rules
occurs frequently. Regions that observe Daylight Savings Time have overlaps in the “fall-back” period when a particular hour is repeated. Therefore, it is recommended to keep the data in the current and more accurate format to avoid any potential complications in the future.
When dealing with
efficient searching
, it is recommended to either convert the time ranges to UTC prior to processing or generate a non-persistent computed column that reflects Mountain Time. It is important to research the historical regulations that govern DST start and end dates and create a NonClustered Index on the computed column.
Solution 2:
While Srutzky’s solution may be resilient, considering the description of a single location and reporting time zone, the simplest solution would be to convert to MTN during ETL or change the server’s settings. It’s unnecessary to build a
cruise ship
when a
row boat
would suffice.
Nonetheless, it would be prudent to seek validation from a business user regarding your approach. While they may not disclose the confidential executive strategy for establishing an office in Naples, they can certainly verify the importance of UTC.
It’s important to consider if it makes sense to convert to MTN. For example, if your e-commerce site receives purchases at –PM EST and you change it to –PM MTN, the data may become corrupted and less useful for analysis.