PHP: Handling daylight savings for time series charts
In this post, I discuss how I handle daylight savings transitions in time series charts on my weather station website.
Daylight savings (DST) can be hard to handle well in time series charts. A lot of people might not care about chart oddities due to DST, but I'm a bit of a perfectionist. Seeing my time series weather charts act strangely during DST transitions always bothered me, so when I rebuilt my weather station website I made sure I handled these transitions nicely.
By nicely, I mean the charts display the data as a continuous time flow, with no overwriting or backward time jumping occuring during DST end and no gaps/jumps occuring during a DST end transition. For example, check out the charts produced by the weather station software running on my home PC (click thumbnails to view larger images):
The time series data
In my situation, the data looks like this:
Time Temperature 2014-01-01 00:00 19.2 2014-01-01 00:05 19.1 2014-01-01 00:10 19.1 ... ...
When daylight savings ended in 2014, here is how the data appears. The third timestamp has jumped back to 02:00 (Daylight savings ends 3am, so clocks jump back one hour).
Time Temperature 2014-04-06 02:50 15.9 2014-04-06 02:55 15.9 2014-04-06 02:00 15.9 ... ...
If we just throw this data as it is into a chart, we don't get what we want to see. Some programs
will simply ignore the earlier timestamps and replace them with the latter duplicates, and some will use
both and give a graph like shown above in the first image. Assume, in PHP, I have created an array
$data which stores the data for 6 April 2014.
Array
(
[0] => Array
(
[time] => 2014-04-06 00:00
[temperature] => 17.3
)
[1] => Array
(
[time] => 2014-04-06 00:05
[temperature] => 17.2
)
*** Additional lines omitted ***
[287] => Array
(
[time] => 2014-04-06 23:55
[temperature] => 15.9
)
[288] => Array
(
[time] => 2014-04-07 00:00
[temperature] => 15.8
)
)
Create a DST indicator array
Here is PHP code showing the creation of an array which stores daylight savings
indicators, $isDst. The explanation is after the code:
// Make sure you work in the timezone where the data was collected
date_default_timezone_set("NZ");
// Set initial dst.
$dst = 0;
// array for storing DST indicator
$isDst = array($dst);
$n = count($data);
for ($i = 1; $i < $n; $i++) {
$t1 = $data[$i - 1]['time'];
$t2 = $data[$i]['time'];
// Get the portions of the date/time values
list($date1, $timestamp1) = explode(' ', $t1);
list($date2, $timestamp2) = explode(' ', $t2);
// Use a date where you know no DST transition occurs
$seconds1 = strtotime("2014-01-01 $timestamp1");
$seconds2 = strtotime("2014-01-01 $timestamp2");
// Difference between t1 and t2 in seconds
$diff = $seconds2 - $seconds1;
// Convert first date into unix timestamp
$date1_time = strtotime($date1);
if ($diff < 0 && $date1 === $date2) {
// If we are here, then the second time portion (hh:mm) occurred
// before the first time portion (e.g. 02:55 -> 02:00), and both
// times were on the same date.
// Difference between tomorrow and today, seconds
$secsDay = strtotime("+1 day", $date1_time) - $date1_time;
// If difference is 25 hours, then DST ended
if ($secsDay === 86400 + 3600)
$dst = -1;
} elseif ($diff > 3600) {
// If we are here, then the second time portion (hh:mm) occurred
// more than an hour after the first time (e.g. 01:55 -> 03:00).
// Difference between tomorrow and today, seconds
$secsDay = strtotime("+1 day", $date1_time) - $date1_time;
// If difference is 23 hours, then DST started
if ($secsDay === 86400 - 3600)
$dst = 1;
}
// Record dst indicator
$isDst[] = $dst;
}
I iterate through the data, comparing adjacent times. So for each iteration of the
loop, we are looking at two timestamps. For each timestamp, I separate the date part
and time part. I calculate unix times (seconds since 1970) for both time portions, but
with the date fixed to a date which has no daylight saving transition. Then I calculate
the difference in seconds, $diff.
We know if the difference is negative that either a new day has started and the timestamp
has gone back to 00:00, or possibly DST ended. If the difference is larger than an hour,
we can only say it is possible DST started (there could be missing data). To verify, we
calculate the difference in seconds between the current date and the next day, $secsDay.
So, if $diff is negative and the dates for the adjacent timestamps are equal and
$secsDay equals 86400 + 3600, then we can be certain we detected DST end. Similarly, if
$diff is over 3600 and $secsDay is 86400 - 3600, then we can
be certain that we detected DST start. We keep a record of the DST indicator for each timestamp,
in the $isDst array.
Create a new time variable
So now we have an array of DST indicators for each timestamp. What we do next is create a new time variable which will be continuous across DST transitions. This new time variable simply takes into account whether DST is in effect or not and adjusts the timestamps accordingly. To get the expected results, you need to switch the timezone back to UTC.
date_default_timezone_set("UTC");
$newTime = array();
for ($i = 0; $i < $n; $i++) {
$newTime[] = strtotime($data[$i]['time']) - $isDst[$i] * 3600;
}
Here are some examples of output from this code. The first block of output shows the results from a DST end transition, the second shows the results from a DST start transition. The output shows that the new time variable remains continuous across DST transitions - yay!
Time Temperature isDst newTime increment
2014-04-06 02:40 15.9 0 1396752000 300
2014-04-06 02:45 15.9 0 1396752300 300
2014-04-06 02:50 15.8 0 1396752600 300
2014-04-06 02:55 15.7 0 1396752900 300
2014-04-06 02:00 15.8 -1 1396753200 300
2014-04-06 02:05 15.9 -1 1396753500 300
2014-04-06 02:10 15.9 -1 1396753800 300
2014-04-06 02:15 15.8 -1 1396754100 300
Time Temperature isDst newTime increment
2014-09-28 01:40 17.3 0 1411868400 300
2014-09-28 01:45 17.3 0 1411868700 300
2014-09-28 01:50 17.2 0 1411869000 300
2014-09-28 01:55 17.2 0 1411869300 300
2014-09-28 03:00 17.2 1 1411869600 300
2014-09-28 03:05 17.2 1 1411869900 300
2014-09-28 03:10 17.1 1 1411870200 300
2014-09-28 03:15 17.0 1 1411870500 300
Conclusion
I then use the $newTime variable as the x data for my charts,
and I create formatter functions for my x-axis and tooltips which map
the x values to the original timestamps, and display those instead. On my
weather site, the final chart for 6 April 2014 looks like the chart below. Note the extra wide
time interval between 12am and 3am - this is actually a 4 hour period.
This might seem like a hacky solution, but after spending many hours getting nowhere it is the best I could come up with, and maybe it could help others too. If you know a better way to handle DST for time series charts, I'd love to hear it - please comment below! :)