1

We've been working on implementing timezone support for our Web app.

This great SO post has helped us a bunch: Daylight saving time and time zone best practices

We've implelmented the OLSON TZ database in MYSQL and are using that for TZ conversions.

We're building a scheduling app so:

We are storing all our bookings which occur on a specific date at a specific time in UTC time in DateTime fields and converting them using CONVERT_TZ(). This is working great.

What we aren't so sure about is stuff like vacations and breaks:

Vacations are just Date references and don't include a time portion. Because CONVERT_TZ() doesn't work on date objects we are guessing that we are best to just store the date value as per the user's timezone?

id1 id3 startDate   endDate
-----------------------------
3   6   2010-12-25  2011-01-03
4   3   2010-09-22  2010-09-26

Same thing with recurring breaks during stored for each day of the week. We currently store their breaks indexed 0-6 for each day of the week. Because these are just time objects we can't use CONVERT_TZ() and assume we should just store them as time values in the user's time zone?

bID sID dayID startTime  endTime
--------------------------------
1   4   1   12:00:00    14:00:00
2   4   4   13:30:00    13:30:00

In this case with vacations and breaks we would only compare them to booking times AFTER the booking times have been converted to the user's local time.

Is this the correct way to handle things, or should we be storing both vacations and breaks in some other way so that we can convert them to UTC (not sure how this would work for breaks).

Thanks for your assistance!

Community
  • 1
  • 1
salonMonsters
  • 1,237
  • 3
  • 16
  • 26
  • I'm guessing from the lack of response that either I've asked a stupidly easy question or this is an acceptable way of handling just time or just date in timezones? – salonMonsters Nov 10 '10 at 17:51

2 Answers2

1

The two storage formats look fine. You just need to convert them to the user's local time when you pull them out of the table.

Actually, for the breaks table I presume they're already nominally in local time, so you just compare directly against the local time of the appointment.

grahamparks
  • 16,130
  • 5
  • 49
  • 43
  • Thanks, I think we'll save the breaks in local time and compare them to the (converted) local time retrieved from MYSQL using TZ_CONVERT and the user's timezone. – salonMonsters Nov 17 '10 at 18:10
0

I don't understand your question well enough to say my answer is 100% correct for you. But I think what you need to do is store the DateTime in "local" time and also store the timezone. This way you have it correct even if daylight savings time shifts (which happens).

Good article at http://blogs.windwardreports.com/davidt/2009/11/what-every-developer-should-know-about-time.html (yes by me).

David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • Hi, thanks for your post. I'm a little confused because your recommendation to save in local with the offset goes against most other articles I've read. From my understanding by using the Olson db in MYSQL and saving in UTC and using TZ_CONVERT when retrieving we solve the problem of any changes to DST start/end dates: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices , http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices , http://stackoverflow.com/questions/11975/handling-timezones-in-storage – salonMonsters Nov 17 '10 at 18:06
  • Yep, I know everyone says use UTC and convert. On the flip side, I also keep seeing stories of software, including Outlook and the iPhone, that have problems when DST changes. We've never had a problem with this apporach. – David Thielen Dec 01 '10 at 06:00