As companies continue to grow in scope and exposure, the importance of storing time in a database grows. Most companies have exposure to more than one time zone. Others, with international impact, find themselves with a larger impact.
I, along with a large number of you, having worked with companies whose presence spans multiple continents, have found it necessary to store data in a way that it can be presented in a cohesive fashion. The problem might be stated thusly: the sales activity for any calendar day, from any company segment, may have a difference in hours from a different segment. Sales in India, for example, may be completed before one completes their morning shower in the USA.
Merging something like sales data from multiple continents requires planning. There are some industry standard techniques. One is to store all time zone sensitive data with a standardized date/time, with a time zone offset. Some use GMT as the standardized time zone. Others use the time zone of the corporate office. GMT has a more universal value, and can make integration of new data sources, often having a GMT data available.
Some database architects prefer to store two times. They store activity in both GMT with a time zone offset, along with the date/time according to their specific time zone. This makes enterprise reporting simpler, because Friday sales in one region may be consolidated with Friday sales in another region without having to do date math on the GMT value and the offset in order to find Friday data. Friday becomes a virtual designation, unique to each time zone.
Do you have a date storage preference? How does it make consumption of the data more flexible, or easier to consume? Get into the conversation. Share your experience in with your comments.
Cheers,
Ben