Dates ar another area where many people convert to PHP format and do lots of calculations using PHP. Often though the same calculations can be done in the query itslef.
Adding and taking away from dates
You might want to schedule something a week from today by storing a date/time in a field. To get that date/time you can use ADDDATE:
$query="INSERT INTO tasks (tasktime, task) VALUES (ADDDATE(NOW(), 7), 'Call John');";
Create a table called tasks with at least two fields to test this (tasktime should be DATETIME and task VARCHAR).
There is also a more sophisticated way to use this function:
$query="INSERT INTO tasks (tasktime, task) VALUES (ADDDATE(NOW(), INTERVAL 1 MONTH), 'Call John');";
That will set a task for a month from now. You can also use YEAR or WEEK.
You do not need to use NOW() as the base date you could use an existing DATETIME (e.g. get the existing value from a field and add one day to it) or manually insert a base time (from an HTML form maybe).
SUBDATE does the same but takes away. Both can handle INTERVALs of HOUR, MINUTE or SECOND as well as dates.
Adding and taking away from times
If you want to add hours, minutes or seconds use ADDDATE with the INTERVAL set.
If you want to add a whole time the time must be in a set format (hours, minutes, seconds and optionally days or a decimal part of a second):
$query="INSERT INTO tasks (tasktime, task) VALUES (ADDTIME(NOW(), '01:00:00'), 'Call John');";
$query="INSERT INTO tasks (tasktime, task) VALUES (ADDTIME(NOW(), '1 01:00:00'), 'Call John');";
$query="INSERT INTO tasks (tasktime, task) VALUES (ADDTIME(NOW(), '01:00:00.1'), 'Call John');";
In order those three:
- Set a task for an hour from now
- Set a task for tomorrow an hour from now
- Set a task an hour and 100 milliseconds from now
SUBTIME does the same but takes away.
Extracting parts of a date/time field
There are functions to get the current date or current time (CURDATE and CURTIME) but you may have a stored DATETIME value but only want the date (or time). You could use PHP and EXPLODE the data but the easier way is to extract the bit you want in the query:
$query="SELECT task FROM tasks WHERE DATE(tasktime)='2010-12-31'";
That would get all tasks for that day. The day itself could have come from CURDATE, a form or be hard coded in as in this example. Replace DATE with TIME to get just the time.
Differences
DATEDIFF gives the difference between two dates or two DATETIME values:
$query="SELECT DATEDIFF(CURDATE(), tasktime) FROM tasks";
The difference between the current date and the date the task was due is given in days. Any time value is ignored.
TIMEDIFF does the same but for time or DATETIME values (you cannot mix time and DATETIME though). The difference is shown in hours, minutes and seconds (so a difference of one day shows as 24 hours).
Extracting any part of dates and times
You can extract just part of the information in a DATETIME field. For example to get the number of the day of the week from a DATETIME (i.e. a number from 1 to 7):
$query="SELECT DAYOFWEEK(tasktime) FROM tasks";
Replace DAYOFWEEK with DAYNAME for the actual name of the day or use DAYOFMONTH, DAYOFYEAR, HOUR, MICROSECOND, MINUTE, MONTH, MONTHNAME, QUARTER, WEEK (1 to 53), WEEKDAY, YEAR or YEARWEEK (year and week).
Formatting dates and times
As well as extracting the bits and reassembling them how you want them you can do it all in one:
$query="SELECT DATE_FORMAT(tasktime, '%D %M %Y which is a %W') FROM tasks";
That query will result in a string such as "14th July 2010 which is a Wednesday".
TIME_FORMAT works in a similar way.
The full list of possible date and time formats is found on the MySQL site.
Other time and date functions
There are more possibilities so if you want to do something with date or time which you cannot do go to the MySQL page on date and time functions..
