0

So, there is absolutely no reason why we should be having this problem in this day and age, but we do. Our database has datetime columns, and when the dates are pulled out from the database, they are retrieved as CDT (this time of year, CST in the winter). That time is then passed as CDT to the UI through JSON. This could not be more wrong.

The time stored in the database is the time relative to the location specified in the data. So, if we have a trip going from Los Angeles at 6AM PDT to New York at 11PM EDT, then the start time will be retrieved from the database as 6AM CDT and the end time will be retrieved as 11PM CDT.

Requirements:

  1. The UI needs to display in the time local to the data, 6AM and 11PM in the previous example.
  2. The UI needs to indicate items in the near future, such as arrivals within the next 3 hours.
  3. When the data is edited, it needs to be input in the same manner, the user enters 6AM in Los Angeles, and 11PM in New York.
  4. The user also needs to be able to enter times relative to the current time, such as "H+5" for 5 hours from now.
  5. The UI needs to sort based on time. This is more of a nice to have as the application they are used to using doesn't do this right either.

Our current solution is just burying our head in the sand and displaying it (untested for browsers in other timezones, such as those in our California office), which is actually surprisingly effective, even though it is not at all semantically correct.

  1. 11PM CDT is read from the database
  2. It gets displayed as 11PM, and is understood by the user to be EDT
  3. The user edits it, puts in 10PM
  4. It is parsed back as 10PM CDT and saved that way, exactly as we want it.

Where the current solution fails miserably is in times relative to the current time.

  1. 11PM CDT is read from the database
  2. It gets displayed as 11PM, and is understood by the user to be EDT
  3. The user edits it, puts in "NOW" (assume the user's local clock is 9PM CDT)
  4. It is parsed back as 9PM CDT and saved that way, but it should have been 10PM because it is 10PM in New York!

I'm looking for a way to handle these five cases that isn't totally hideous. I am open to a solution in any layer (architecture detailed above), but there are constraints because we share the database with another application. If there are additional tools/frameworks that would be useful and fit with what we already have, I am open to using them.

  • Database: SQL Server 2008
  • API: Rails with JSON responses
  • Frontend: JS + Moment + other stuff unrelated to dates
  • Any attempt to correct the data and/or schema is totally out of the question as we would break the other application.
  • The addition of new views/table columns/tables/stored procedures is usually possible.
  • The addition of indexes is NOT allowed. The status of any more exotic features is unknown.
  • There are many tables/endpoints that are affected by this problem, so any brute-force solution is going to be incredibly tedious.
  • Any solution only needs to work in the Continental US.
  • Note that this is not a simple timezone conversion as the timezone we get back from the database is straight up wrong, so conversions of the timezone will also be wrong.
polson136
  • 151
  • 1
  • 10
  • 1
    working with timezones, especially when combined with daylight saving time, is enough to drive most programmers crazy. I can only describe it as a total nightmare, especially when you are computing the time zone based on geographic location rather then on nationality. You really, really, really want to avoid it whenever you can. Keep your dates as UTC whenever you can, let local machines convert from UTC to local time. – Zohar Peled Jul 09 '16 at 09:55
  • Agreed. I would LOVE to, but we don't have control over the database. Any advice on getting them to UTC time? Also, in case I wasn't clear, the time that needs to be displayed/stored in is the time in the location of the data, so a time in Denver, CO needs to be displayed as MDT, no matter where the user is. – polson136 Jul 09 '16 at 17:13
  • Please provide some specific details. 1) What format does the time data look like within the JSON? Give an example. 2) What time zone information is present in the data? You mention LA and NY, but are there actual time zone IDs present, such as `America/Los_Angeles`, etc.? 3) When you say the user enters `"H+5"` or `"NOW"` - are you sending those strings all the way back to the server? Why not just catch it on the client and use their current local time? – Matt Johnson-Pint Jul 09 '16 at 21:08
  • 4) You say "I'm looking for a way to handle these four cases" - but I count only three: the client entering a specific time, the client entering `"H+5"` and the client entering `"NOW"`. What is the fourth case? 5) What have you actually *tried*? Can you show us some code of specific points? Your question is likely to be closed as "too broad" in its current form. Can you narrow it down please? Please read [about how to ask](http://stackoverflow.com/help/asking) in the help center. You should also read [Daylight saving time and time zone best practices](http://stackoverflow.com/q/2532729). – Matt Johnson-Pint Jul 09 '16 at 21:09
  • Matt Johnson: 1. The standard Ruby format of YYYY-MM-DDTHH:mm:ssTZD is used for transport between UI and API 2. It is read from the database as "America/Chicago". I'm not a database expert, but it is possible that it is being added in the ODBC connection settings, not the data itself. Regardless, that information is completely inaccurate. 3. No, those strings are interpreted on the client, but they cannot be calculated using the clients time. For example, if the client is in Chicago, and wants to save the date for a location in Los Angeles, the time that must be saved is Los Angeles time. – polson136 Jul 11 '16 at 03:38
  • ... For example, if they want to store "now", and it is currently 7:00 CDT, the time that must be saved is 5:00 CDT since it is currently 5:00 PDT. Yes, it is as crazy as it sounds. 4. The display also counts for 3 cases (display of date, sort of date, display of dates within N hours). "NOW" and "H+5" I counted as the same case since one is a calculation of the other. I apologize, the original post should have said 5. I will edit it. – polson136 Jul 11 '16 at 03:45
  • "Daylight saving time and time zone best practices" - There is a lot of info there and I could have missed something, but the Moment Timezone library was the only useful thing I saw there, and it may very well be part of the solution, but that is using a client-side library to correct data tagged with incorrect timezones, which it was never meant to do. Everything else is either not useful to my specific problem (like making sure that all the computers' clocks are set correctly) or makes the nieve assumption that I can control my database format, which I can't do. – polson136 Jul 11 '16 at 03:49
  • I suppose a different of looking at the problem is adding timezone information to data that doesn't currently have it, at least not in any useful manner. – polson136 Jul 11 '16 at 03:51
  • Here's another example: Suppose there are 4 rows of data, in Los Angeles, CA (PDT), Denver, CO (MDT), Chicago, IL (CDT), and New York, NY (EDT), and in all 4 rows the date is 9:00 CDT. The data should be displayed as follows: New York: 9:00, Chicago: 9:00, Denver: 9:00, Los Angeles: 9:00. If sorted from earliest to latest: Los Angeles, Denver, Chicago, New York. If set to now, should be stored as: Los Angeles: 9:18 CDT, Denver: 10:18 CDT, Chicago: 11:18 CDT, New York: 12:18 CDT. – polson136 Jul 11 '16 at 04:22
  • So, it sounds as if instead of storing everything in UTC you want to store everything in central, and everything else spirals out from that. You need moment timezone for this, because the JS date object cannot understand time zones other than UTC or the user's local time. If you want to take a time in the browser's local time zone and convert it to US central, the code would be as follows - moment(localTimeFromUser, format).tz('America/Chicago') – Maggie Pint Jul 11 '16 at 04:39
  • If you wish to convert from central to local do moment.tz(centralTimeValue, 'America/Chicago').local() – Maggie Pint Jul 11 '16 at 04:41
  • I don't need a timezone conversion. That would be easy. If you look carefully at my examples, the date in the database only LOOKS like it is Central TIme. It isn't really. 9:00 PDT needs to be saved as 9:00 CDT. With your logic, 9:00 PDT would become 11:00 CDT. – polson136 Jul 11 '16 at 13:39

0 Answers0