-1

I have a tool that I am working on which allows people to create meetings. They can select the Date, Time & Timezone that this meeting is occurring in.

I need to determine the best way to store that data in SQL.

The end goal is that a user can click on "View Meetings" and it will show them all of the meetings. However, they also need to be able to select the timezone that they want to view the meetings in and that needs to changes the times to reflect what they have picked.

What is the best approach to accomplish this?

2 Answers 2

2

As a person who has had to fix timezone issues, ALWAYS store the data in UTC. Translate it to the timezone they selected for display only.

If SQL Server allows you to store the UTC offset as part of the datetime, then it's not as big of a deal. However, in MySQL, the date is just stored absent of timezone context. Here's a case in point, which I hope I can make to make sense :)

The user saves a time 2:00 EST for the day DST is removed. Then, let's say you store it in Central time, so it gets stored as 1:00 CST (but the CST part is not stored in MySQL). When the code pulls it out as 1:00am, it just assumes DST is still active because it hasn't been forced back. Then, for display, you change it back to the user's timezone, which bumps it ahead one hour to 2am EDT. However, at 2am EDT, the timezone gets bumped back to 1am EST. So, the user entered 2am EST but resulted in 1am EST on the way out.

I am trying to fix some legacy code with exactly this problem. In our logs, because of this issue, when we try to graph out that timeframe, we end up with 3 hours of 1am-2am instead of 2 for the transition and the 2am - 3am time is completely lost for eastern timezone. Problems only occur twice a year, and the transition away from DST is the worst...but it never would have been a problem if the times had been stored in UTC since UTC does not have any time changes, ever.

This may not be a problem you will encounter in your use-case, but, IMHO, it is best to just always store in UTC and translate on the view, as this has no drawbacks that I am aware of for any kind of application that has to deal with multiple timezones already.

One small exception, however...let's say that you are storing a recurring event. It happens at 18:00 Eastern on Tuesdays all year. For such an example, if you stored it as 23:00 UTC...part of the year, the event would run at 19:00. So, for such an example, you just want to deal with the timepiece separate from any date context.

[UPDATE] PHP timezone parsing

$dataFromDb = $row;
// get it from DB and assume DB is in UTC
$myDateTime = new DateTime($row['dateTime'], new DateTimeZone('UTC'));
// convert it to desired timezone
$myDateTime->setTimezone( new DateTimeZone('America/Chicago') );
echo $myDateTime->format('c'); //format in ISO-8601 format (very transportable)

You can then send the ISO format to JavaScript. However, JavaScript will take whatever time you send it and display it in the user's LOCAL timezone. So, if you want to display Eastern time even to someone in Central time, you'll need to force it into the Eastern timezone in JS. This is a PAIN without momentjs. So, if you don't want it to display the time in the user's local time, and you don't want to add momentjs to your project, then it is MUCH easier to just handle all the conversions in PHP and send the string as you want it formatted from PHP and don't let JS mess with it.

To Save to DB, same process:

//get selected timezone and date/time from user:
$dateTime = new DateTime( $_POST['dateTime'], new DateTimeZone($_POST['timezone']));
$dateTime->setTimezone( new DateTimeZone('UTC') );
$dbColumn = $dateTime->format('Y-m-d H:i:s');

NOTE: I'm not doing validation in the above for simplicity and assuming the DateTime gets created correctly...don't actually trust that data from user will actually become a valid date.

7
  • Thanks for the detailed response! I am trying to figure out now how to convert a date to UTC so I can store it that way.
    – SBB
    Commented Jan 8, 2015 at 18:38
  • @SBB, take a look here for getting the data out: stackoverflow.com/questions/8096679/… Commented Jan 8, 2015 at 19:05
  • @SBB, to modify it for display, check here: xiirus.net/articles/… Commented Jan 8, 2015 at 19:07
  • Thanks, Sadly I'm not using something like NET that would probably make this much more simple. I can get the offset from javascript of the client and pass that to my code. I then need to determine how to convert the UTC to another date based on the offset viewing it.
    – SBB
    Commented Jan 8, 2015 at 19:11
  • @SBB, PHP is actually easier than .NET...but for JS, use momentjs. If you format the output from your server as YYYY-MM-DDThh:mm:ssZ (adding a T before time and a Z at the end or +00:00), then momentjs will automatically take the time, understand it as UTC time, and convert it to the user's local time on their machine. If you need to display it in a specific timezone, other than the end-user's, then you can use moment's timezone package: momentjs.com/timezone Commented Jan 8, 2015 at 21:31
2

If you are using SQL Server 2008 or higher, I recommend the DATETIMEOFFSET data type. It stores exactly what you want: date + time + time zone offset. This means that you don't have to write code to translate the value to/from UTC. It also means that no one can forget to translate the value to/from UTC and corrupt the data.

PS: This data type translates directly to the DateTimeOffset data type in .NET code, if that helps.

4
  • What's the best way to take a given time and convert it to that data type? I have a date / time selection on the front end. I will add in a time zone selection so they can choose what time zone the meeting will occur in. However, I need to figure out how to take that date, time and time zone offset and convert it to the datetimeoffset
    – SBB
    Commented Jan 9, 2015 at 12:57
  • It depends on the language you are using. In .NET code, just pass all the values into the DateTimeOffset constructor. If you are building a T-SQL literal value, then it must be in the format YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm], e.g. '2015-01-09 13:16:04.123 +01:00'. I can't help you with other languages I'm afraid. Commented Jan 9, 2015 at 13:15
  • Great, last question! From that format, how would I go about saying "show this this record, but in this time zone"?
    – SBB
    Commented Jan 9, 2015 at 13:16
  • How about DateTimeOffset.ToOffset? Commented Jan 12, 2015 at 8:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.