How to deal with datetime column when some are adjusted for DST?

Question

I am doing some comparisons between datetime in MySQL with PHP. In MySQL time is incremented by 1 between Nov 2, 2014 and March 8, 2015 due to daylight saving time.

So far I have if statements that check whether I need to subtract 1 hour before comparing two datetimes or not. It is only good for 2014/2015 and I wonder if there is way to do the same for previous years without hardcoding the exact dates as I did above?

The comparison must be made on the fly in PHP, cant modify schema.

HERE IS SOME DATA:

2014-10-15 18:00:00

2014-10-22 18:00:00

2014-10-29 18:00:00

2014-11-05 19:00:00

2014-11-12 19:00:00

2014-11-19 19:00:00

So, all of these guys are suppose to have the same time and to compare them I need to modify on the fly those that are after Nov 2.

CLARIFICATION: All I need is to take 2014-10-29 18:00:00 and 2014-11-05 19:00:00 (thats how they are in MYSQL) and tell whether this dates have THE SAME TIME ONLY. Due to DST, the server incremented the second entry to 19:00:00 hrs and I need to handle that in PHP with whatever built in stuff it has.

E.g.

$d = new DateTime('2014-10-29 18:00:00', new DateTimezone('America/New_York'));

$dd = new DateTime('2014-11-05 19:00:00', new DateTimezone('America/New_York'));

echo $d->diff($dd)->format('%h:%m:%s'); <<<<Should give 0 hr difference in my case

Rule #1) don't store date/time with time zone; always use UTC. — Kermit, Aug 11 '14 at 19:42
@Kermit My rule #1 (real): Always include the TZ offset ;-) Saving as UTC loses vital information in my experience. — user2864740, Aug 11 '14 at 19:44
@user2864740 - it appears you are not aware of `timestamp` and what it does. — N.B., Aug 11 '14 at 19:44
I will remember your suggestions. But, I dont have control over whatever information is already stored in db. How do I proceed then? — jump3r, Aug 11 '14 at 19:46
@jump3r create a new column then update to set that column with an adjusted time if it fits in the range of bad time zones. — Kermit, Aug 11 '14 at 19:47
if you can't change the data in the database and aren't allowed editing rights then maybe you should look at using `convert_tz()` works for converting between timezones — John Ruddell, Aug 11 '14 at 19:47
I cant modify schema and need to do everything on the fly in php, would really appreciate some example. — jump3r, Aug 11 '14 at 19:54
@jump3r if you want help with this then you need to post data.. give us some examples of your output, data, and desired outcome — John Ruddell, Aug 11 '14 at 19:56
What comparisons are you trying to make, exactly? In general, you can create php `DateTime` objects with the correct time zone and it'll correct for DST automatically. — Sam Dufel, Aug 11 '14 at 20:08
@N.B. How does my comment make me "not aware"? When I deal with times I save the TZ offset. There is nothing about "not being aware" in my statement/recommendation - I find such imposition rude. My recommendation does not change if MySQL lack such a builtin datatype and requires manual handling. In cases where real-world times matter, UTC is often missing important information (one might even argue that DST information should also be included in a TZ offset) and UTC can be derived from local+offset (or vice-versa). Thus, if anything, only saving UTC is denormalized data that loses information. — user2864740, Aug 11 '14 at 20:14
@user2864740 - it's not rude. Store the offset if you will, your comment just proves you are not aware of what timestamp is nor how to use it properly. It's a fact, I'm not undermining your "authority" or anything. Eventually, one day, you'll come to terms with facts. — N.B., Aug 11 '14 at 21:02
@N.B. It was snidely worded, as is was the last comment using aggressive personal pronouns and language. Compare to an argument against storing a TZ and/or an actual fact, like "MySQL/Standard SQL DATETIME/TIMESTAMP types do not support timezones", as opposed to conjecture about my understanding. I've provided information on why it is often *important* to store the TZ. The zero-information counter arguments: "you are not aware [of how to store times]" and "you'll come to terms with facts". — user2864740, Aug 11 '14 at 21:21

score 1 · Answer 1 · answered Aug 11 '14 at 20:12

1

I would recommend the following approach:

First, initialize all times as DateTime objects, and pass the correct timezones as second argument:

$date1 = new DateTime('2014-03-01 12:00:00', new DateTimezone('Europe/Berlin'));

With this object, you have a bullet-proof DateTime object for comparing with others.

If you want to do mathematical operations, such as adding or subtracting with others, you can use the $date1->add() and $date1->sub() methods; but if you just need the absolute offset, it may be easier to convert each DateTime object to UTC, using $date1->setTimezone(new DateTimezone('UTC')), and work with simple UNIX timestamps generated by the $date1->getTimestamp() method.

By the way, note that the following initialization does not have the desired result:

$date1 = new DateTime('2014-03-01 12:00:00');
$date1->setTimezone(new DateTimezone('Europe/Berlin'));

In fact, this has the opposite effect – it interprets the time as UTC and then converts it to a local timestamp.

answered Aug 11 '14 at 20:12

lxg

12,375
12
51
73

Hi, thank you for the answer, could you show me what to do next. So, I have $date1 & date2 with '2014-10-29 18:00:00' and '2014-11-05 19:00:00' and whatever timezone I set up. How do I extract and compare time of the two objects? I really didnt find anything in documentation. – jump3r Aug 11 '14 at 20:52
If it's just about comparing, you can use the comparison operators (`<`, `>` or `==`; don't use `===`) on the `DateTime` objects. As for other operations, the “proper” way would be to use the `diff()` method and work with `DateInterval` objects. But if both of the `DateTime` objects have the same timezone, you can simply subtract their UNIX timestamps, this will give you the time difference in seconds. – lxg Aug 11 '14 at 21:05
@Ixg - you don't need to convert DateTime objects to the same time zone to work with them; the offsets are handled automatically by the library. Also - unix timestamps are always defined based on the UTC time zone. – Sam Dufel Aug 11 '14 at 22:22
@SamDufel I did't say that you _must_ convert to the same timezone, I just meant to say that it makes some operations more intuitive. Thanks for pointing out that `DateTime::getTimestamp()` always returns UTC, I wasn't aware of that. – lxg Aug 11 '14 at 22:33

Sam Dufel · Accepted Answer · 2014-08-12T14:58:04.787

1

If you're looking for the time difference between two dates, you can do the following:

 // Use whatever timezone you recorded the times in.
 $date1 = new DateTime("2014-10-15 19:00:00", new DateTimeZone("America/Los_Angeles"));
 $date2 = new DateTime("2014-11-19 18:00:00", new DateTimeZone("America/Los_Angeles"));

 // If you'd like to ignore the time zone differences, just switch the dates 
 // to a time zone without DST

 $utc = new DateTimeZone('UTC');
 $date1->setTimezone($utc);
 $date2->setTimezone($utc);

 echo "Time difference: " . $date1->diff($date2)->format('%H:%I:%S');

edited Aug 12 '14 at 14:58

answered Aug 11 '14 at 22:19

Sam Dufel

17,560
3
48
51

That will still give 1 hr difference – jump3r Aug 12 '14 at 13:49
No Sam, in my case I need to find recurring entries with same intervals. The time change does not allow me to do that smoothly without manually checking month and day with if statements to see if I need to subtract one hour and then compare – jump3r Aug 12 '14 at 14:30
now it is closer, but instead of decrementing it increments so the difference is up from 1hr to 2hr – jump3r Aug 12 '14 at 15:22
@jump3r - you're looking at dates in the wrong direction. After Nov 2, times shift 1 hour backwards. 19:00 on nov 1 == 18:00 on nov 2 – Sam Dufel Aug 12 '14 at 16:30
You are right... I am screwed. Thanks for the answer. – jump3r Aug 12 '14 at 16:41

How to deal with datetime column when some are adjusted for DST?

2 Answers2