0

This is more of a design question.

I have a MySQL DB containing stock exchanges data. There are several stock exchanges across the world, all in different time zones. They all publish data that I want to load in my DB in their local timezone (along with time changes during daylight savings). The data I am interested in is usually in the format of:

DATETIME             | STOCK | PRICE
______________________________________
2017-01-01 13:00:00  | AAAAA | 34.56
2017-01-01 13:00:00  | BBBBB | 14.82

I will have users that are all in the EST timezone and my server is currently in UTC. I need the users to access this data in the market's local timezone quite easily. At the moment, they use Excel's MySQL connectivity to load data into their Excel sheets to use it. I might eventually need to compare timestamps between different markets. Some markets observe daylight savings, some don't.

The options I see are:

1. Store everything in UTC
2. Store everything in the local timezone
3. Store everything in the local timezone + keep another column to specify which timezone (i.e. EST, EDT, PST, PDT)

Option 1 has the disadvantage that users would need to convert the UTC time to their local timezone by themselves before using it. The advantages are that a single field handles daylight savings and that we can easily compare the timestamps between markets.

Option 2 has the disadvantage of not keeping the time changes information (daylight savings) and would effectively 'lose' one hour every year. Not really a viable option in my case.

Option 3 has a slightly more complex DB schema and wouldn't make it easy to compare timestamps in different markets, but the users wouldn't have to worry about converting UTC time to the market's local time while still keeping track of daylight savings.

What is the 'standard' way of handling these situations?

Vincent L
  • 699
  • 2
  • 11
  • 25
  • SO is not an appropriate site for design discussions. – Barmar Sep 22 '17 at 03:48
  • 1
    Just go with UTC and store the time-zone of the exchange itself elsewhere. Time zones change constantly and are a source of immense frustration for anyone dealing with temporal data. If you ever want to know local-time, use a time conversion library to do it properly. You can always render out UTC times as local time if necessary for display purposes. – tadman Sep 22 '17 at 04:07
  • Thanks for you answer tadman. The issue is that an exchange can have 2 timezones. For instance New York has EST and EDT – Vincent L Sep 22 '17 at 04:22
  • @VincentL New York has only one time zone, `America/New_York`, also known as `EST5EDT`. Store as UTC, but don't store times -- store datetimes -- so that time zone libraries can apply the daylight time logic automatically. Then throw away the date part after conversion if not needed for display. Use a view instead of a table for the excel query and let the view handle the conversions. – Michael - sqlbot Sep 22 '17 at 05:44
  • Store all datetime in UTC format and for display date handle it in code – Hardeep Singh Sep 22 '17 at 06:15
  • @Michael-sqlbot - `America/New_York` and `EST5EDT` are not exactly the same. The prior has the rich history of time changes in the US Eastern time zone, and would account for any future changes if there ever are any. The latter does neither. – Matt Johnson-Pint Sep 26 '17 at 16:51
  • @MattJohnson, it seems that you are correct -- they are close enough for many purposes, but "close enough" is indeed not equivalence. Thank you for the correction. – Michael - sqlbot Sep 26 '17 at 17:47

0 Answers0