David Banks

Web development tips and tutorials

PHP: Handling daylight savings for time series charts

22 February 2015PHP

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):

DST end chart
DST end chart
DST start chart
DST start chart

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.

Time series chart

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! :)