time() and date() problems after time change (DST - standard)

Question

In PHP I would like to output an HTML option list containing dates for the next 14 days.

These appointments are always at 18 o'clock:

$today_day = date('d');
$today_month = date('m');
$today_year = date('Y');
$date_entry = mktime(18, 00, 00, $today_month, $today_day, $today_year);
$optionsStr = '<select name="date">';
for ($d = 1; $d < 14; $d++) {
    $date_entry_temp = $date_entry+86400*$d;
    $optionsStr .= '<option value="'.$date_entry_temp.'">'.date('d.m.Y', $date_entry_temp).'</option>';
}
$optionsStr .= '</select>';
echo $optionsStr;

The user can then choose from one of these dates and submit the form. The chosen timestamp is then inserted into the database.

So I have some entries in my database.

On another page there is a list of current appointments:

mysql_query("SELECT id, name FROM appointments WHERE date_time = ".time());

So at 18 o'clock there should be some output as there are entries in the database for that day. This works perfectly good until the time changes from DST to standard time or vice versa. Then, indeed, is wrong:

The appointments are shown one hour too late or too early respectively.

How can I solve this problem?

Answer 1

mktime() creates a unix timestamp. A unix time stamp is the number of seconds from January 1, 1970, 00:00:00 GMT +0000. (Greenwich time)

When you set your timezone to "Europe/Berlin", the timezone is either GMT+0100 (in winter) or GMT+0200 (in summer). This means that the Greenwich time of your appointments changes by one hour when you have DST. That means that the time between the first appointment before the change and the next appointment after the change is not 24 hours, but 23 or 25. However, you generate the appointments by adding 86400 seconds = 24 hours.

You can use the DateTime object and the add() method instead. It takes DST changes into account.

// create a new date object with todays date
$date = new DateTime();
// set the time to 18:00
$date->setTime(18,0,0);
$optionsStr = '<select name="date">';
for ($i = 0; $i < 14; $i++) {
    // add 1 day
    $date->add(new DateInterval('P1D'));
    $optionsStr .= '<option value="'.$date->format('U').'">'.$date->format('d.m.Y').'</option>';
}
$optionsStr .= '</select>';
echo $optionsStr;

See http://www.php.net/manual/en/datetime.add.php for more information.

Answer 2

Your main issue is that you're not working on GMT dates. Here's a colorful post that highlights the resulting pitfalls:

http://derickrethans.nl/storing-date-time-in-database.html

What you should be doing is, store your appointments datetimes at time zone UTC, compare datetimes at time zone UTC, and display datetimes to users in your (ok) or their (ideal) preferred time zone.