Fun with dates

In response to Kai Voigt's post about unxepected results from dates here is what is going wrong.

To add and subtract time using + and - you need to use the INTERVAL keyword either before or after the + or - like so..

mysql> select now() - interval 2 day;
+------------------------+
| now() - interval 2 day |
+------------------------+
| 2006-04-24 07:31:02    |
+------------------------+
1 row in set (0.00 sec)

In Kai's example it's valid to use - on it's own but that handles the date simply as an integer and subtracts the number specified. In small cases as in the example this can seem as if it's the seconds being subtracted but if for example you subtract 100 it simply reduces the number by that amount and not by 1 minute 40 seconds as it sould for a date type.

mysql> select now() - 100 day, now();
+----------------+---------------------+
| day            | now()               |
+----------------+---------------------+
| 20060426073122 | 2006-04-26 07:32:22 |
+----------------+---------------------+
1 row in set (0.00 sec)

You can see above the seconds portion of the first "date" is the same where it should be different if it were dealing with it as a time. You can also see that any date formating has been removed (suggesting something odd is going on).

The second problem is that while you might think that not including the interval would case a problem it becomes a valid SQL statement because it treats the DAY simply as a heading in the output. To change the heading of a column we can either use AS column_name or simply column_name after our column definiton, this can be seen like so..

mysql> select now() - 100 day, now();
+----------------+---------------------+
| day            | now()               |
+----------------+---------------------+
| 20060426073122 | 2006-04-26 07:32:22 |
+----------------+---------------------+
1 row in set (0.00 sec)

mysql> select now() - 100 as "anything you fancy", now();
+--------------------+---------------------+
| anything you fancy | now()               |
+--------------------+---------------------+
|     20060426073738 | 2006-04-26 07:38:38 |
+--------------------+---------------------+
1 row in set (0.00 sec)

So to add and remove time from dates you need to make sure you use the interval. Something to watch out for when you get unexpected results is the column heading, that might give a clue that all is not right with your SQL.