9

I have a general question about dealing with daylight saving time. I guess its not really PHP specific, but I am writing in PHP, so I figure it wouldn't hurt to include it.

I have a calendar app built using jquery fullcalendar. The user views events in their local timezone and my server stores them as UTC datetimes in mysql. (Other questions on stackoverflow suggest that this is the best way to deal with timezones.) So there is a conversion every time the user saves or views events on the calendar. This is working fine, but I am confused about how best to deal with daylight saving time.

For example, say a user in timezone EST (eastern standard time) creates an event when it is NOT daylight saving for 3pm that repeats everyday. My PHP code uses the standard DateTime (and DateTimeZone) class to convert between UTC and EST by UTC-5:00. When daylight saving is active, the clock turns ahead one hour and PHP will instead convert between UTC and EST by UTC-4:00. From the user's point of view, the event is then shifted from the original 3pm to 4pm. This is not what my users and I want. A 3pm event should stay a 3pm event regardless of daylight savings. What is the best way to deal with this? Is there a way in PHP to ignore daylight saving time?

My code:

$getDate = new DateTime($storedDate, new DateTimeZone('UTC'));
$getDate->setTimezone(new DateTimeZone('America/New_York'));
$getDateString = $getDate->format('Y-m-d H:i:s');

MORE INFO: (copied from my comments below)

-Only the first occurrence of a repeating event is stored. All other occurrences are created on the fly according to what calendar view the user requests (month,week,or day views). With the way I have it coded, it creates only the occurrences that will be visible on the view.

-The thing is, I also need to keep it constant for other timezones. To continue the original example, 3pm should stay 3pm in EST (regardless of daylight savings), but if the event is viewed in Central time, the event should also stay 2pm (regardless of daylight savings).

Essentially, I need to somehow ignore daylight saving time.

train
  • 610
  • 2
  • 11
  • 20
  • This is a great question. Have you looked at this: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices – larsAnders Feb 21 '14 at 22:25
  • Also this: http://www.thisprogrammingthing.com/2013/things-i-learned-while-writing-a-timezone-aware-website/ – larsAnders Feb 21 '14 at 22:29
  • I've seen the first link you gave, but the second one is new. Thanks, I'll see if I can find anything. EDIT: I didn't find anything useful. But I did notice that the first link suggests including the original time offset for each event. However, this seems to be a strange and inefficient solution. – train Feb 21 '14 at 22:33
  • When you create repeating events are they all stored as individual timestamps, or do you have some kind of offset from the 1st event? – vascowhite Feb 21 '14 at 22:52
  • Only the first occurrence of a repeating event is stored. All other occurrences are created on the fly according to what calendar view the user requests (month,week,or day views). With the way I have it coded, it creates only the occurrences that will be visible on the view. – train Feb 21 '14 at 23:03
  • What comes to mind is to save the timezone with the event record. When the record is pulled, you can get the time _when the event was created_, pull the hour/minute component out of it, and put that on the calendar. – miken32 Feb 21 '14 at 23:04
  • When you create them on the fly are you doing it with time stamps? You need to do __every__ operation on times in UTC and only convert to users TZ for display, I suspect that you are performing operations/calculations on local times. – vascowhite Feb 22 '14 at 00:45
  • I'm not quite sure how that plays into my question, but i am indeed already doing all my operations in UTC and only converting right before returning the results to the user – train Feb 22 '14 at 00:51
  • It only plays into your question because I'd misunderstood the problem. I've re-read the question and recommend you ignore my previous comment :) – vascowhite Feb 22 '14 at 01:03
  • You may also find this question and it's answer useful: [How to detect Ambiguous and Invalid DateTime in PHP?](http://stackoverflow.com/q/18409405/634824) – Matt Johnson-Pint Feb 23 '14 at 22:01

2 Answers2

2

This is a complicated problem. I've learned the hard way that not every day is 86,400 seconds long.

When working on a calendar application of sorts, I made a design decision early on that saved me a lot of hassle. That is, every instance of an event has an entry in the database.

One table was for all of the event information (title, description, etc.). Another table held a timestamp for each instance of that event. When someone scheduled a repeating event (say 3:00PM every Wednesday), I would insert an instance at 3:00PM in their timezone (which is actually stored as UTC) every Wednesday. Now, events in theory can repeat forever. I decided that it was much simpler to put a reasonable limit (say 50 or 100 years) on the repetition than it would be to have to calculate all of the dates of an event on the fly. To the user, it looks like the event goes on forever, but in the database it doesn't. Even if you have an event scheduled every day for 100 years, that's only 36,500 records in a very narrow table.

With this approach you have to consider exceptions. Sometimes people will change the details of one instance of an event. In those cases, I simply created another event and copied over the relevant details... since it is effectively a separate event. If you wanted to tie them all together with a group ID, you could. Changing individual scheduling of the event is easy since there is a separate row for each instance.

I recommend this approach for most scenarios like this. It saved a lot of hassle for me to be able to rely on a database for all the heavy lifting, and also solves your timezone problem at the same time.

Brad
  • 159,648
  • 54
  • 349
  • 530
  • A reasonable approach, but what happens if DST rules or timezone boundaries change? – miken32 Feb 21 '14 at 22:59
  • miken puts up a good point. The reason I'd like to keep everything stored as UTC is so that I can just update my PHP DateTime component to keep up to date with DST or timezone changes. – train Feb 21 '14 at 23:08
  • @train NO, you don't store in their time zone, you **always** store as UTC. You schedule things in their time zone which are converted to UTC on insert. – Brad Feb 22 '14 at 16:28
  • @miken32 You simply re-calculate. Those changes don't happen often, and are easy enough to deal with using a simple one-off query. Always store as UTC. Always display in the appropriate time zone for the user. – Brad Feb 22 '14 at 16:29
2

Have you tried looking at DateTimeZone::getTransitions() ?

http://www.php.net/manual/en/datetimezone.gettransitions.php

In particular use the [offset] and [isdst] properties.

  • When they save the time, find the first transition before the current date that is NOT DST. (Typically one of the two values in the past year). Convert using the offset of the non-DST period
  • When retrieving the value and you are currently in a DST period use the offset of a non-DST period to translate the time, not the current offset.

Taking your EST example, in August even though you are in EDT, you save values using the EST conversion of -5.

When pulling the value back out if they view that value in January you add 5, and if you are in August you add 4.

This will work for 95% of cases I'm assuming that the switches are consistent. If Eastern decided to merge with Central, you could have transitions that run –5/–4/–5/–4/–5/–5/–6/–5/–6/–6 and that would mess things up.

There's no magic bullet for this one. I don't know the details of your app structure, you may just have to try adding 3 hours to the midnight of whatever day you are on so that any recurring daily appointment is stored as a time only.

Greg Robson
  • 482
  • 4
  • 8
  • The thing is, I also need to keep it constant for other timezones. To continue the original example, 3pm should stay 3pm in EST (regardless of daylight savings), but if the event is viewed in Central time, the event should also stay 2pm (regardless of daylight savings). – train Feb 21 '14 at 23:13
  • In that case extending the second bullet point above, if "Joe" is in CST you need to convert using the time zone he is located in. Does your app have a timezone stored against each user? – Greg Robson Feb 21 '14 at 23:17
  • I use [this](http://pellepim.bitbucket.org/jstz/) to get the timezone with javascript and send that to the server before fetching events. The problem with storing the timezone is that my app also allows non-users (guests) to view the calendar and the events too (read-only though). – train Feb 21 '14 at 23:21
  • Oh, ok. I think I see what you are saying. So essentially, I should be checking for DST on every request? and using the non-DST offset? (or the other way around if I keep consistent?) – train Feb 21 '14 at 23:23
  • Yes. While the javascript library is a good guess, it can't identify the timezone for certain. In some cases, on a given day, an offset could be valid for a Northern hemisphere's country's summer time and a southern hemisphere's standard time. If they are a guest, it's probably best to use the timezone of whoever made the appointment and display it as "3am (local to – Greg Robson Feb 21 '14 at 23:31
  • I agree that getTransitions() is probably the path to a solution. in fact, checking if any DateTime is a DST time is quite easy. http://3v4l.org/g3qXi – vascowhite Feb 22 '14 at 01:35