Working with Dates and Times in MySQL

In this chapter of MySQL Essentials we are going to look at storing dates and times in a database table and also retrieving and manipulating these values.

Contents

Date and Time Formats

MySQL supports a number of date and time column formats. These can be summarized as follows:

• DATE - Stores a date value in the form YYYY-MM-DD. For example 2008-10-23.

• DATETIME - Stores a date and time value of the form YYYY-MM-DD HH:MM:SS. For example 2008-10-23 10:37:22. The supported range of dates and times is 1000-01-01 00:00:00 all the way through to 9999-12-31 23:59:59

• TIMESTAMP - Similar to DATETIME with some differences depending on the version of MySQL and the mode in which the server is running.

Creating Date and Time Fields

A table containing DATE and DATETIME columns is created much the same way as any other columns in a table. For example, we can create a new table called orders which contains order number, order item, order date and order delivery columns as follows:

CREATE TABLE `MySampleDB`.`orders` (
  `order_no` INT  NOT NULL AUTO_INCREMENT,
  `order_item` TEXT  NOT NULL,
  `order_date` DATETIME  NOT NULL,
  `order_delivery` DATE  NOT NULL
)
ENGINE = InnoDB;

The order_date column is a DATETIME field because we want to record the precise time and date that the order was placed. For the delivery date it will be impossible to predict the exact time of day the package will arrive, so we just want to record the date.

Date and Time Formats

Whilst it is most common to store dates using a dash (-) as the delimiter and a colon (:) as the time delimiter it is in fact possible to use any character, or no character between the date and time segments. For example, the following formats all achieve the same result:

2008-10-23 10:37:22
20081023103722
2008/10/23 10.37.22
2008*10*23*10*37*22

As you can see MySQL provides considerable flexibility in how dates and times are formatted.

Date and Time Functions

In addition to providing mechanisms for storing dates and times, MySQL also provides a wide range of functions that can be used to manipulate dates and times. The following table provides a list of the more common functions available for working with times and dates in MySQL:

Function	Description
ADDDATE()	Add dates
ADDTIME()	Add time
CONVERT_TZ()	Convert from one timezone to another
CURDATE()	Returns the current date
CURTIME()	Returns the current system time
DATE_ADD()	Add two dates
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract two dates
DATE()	Extract the date part of a date or datetime expression
DATEDIFF()	Subtract two dates
DAYNAME()	Returns the name of the weekday
DAYOFMONTH()	Returns the day of the month (1-31)
DAYOFWEEK()	Returns the weekday index of the argument
DAYOFYEAR()	Returns the day of the year (1-366)
EXTRACT	Extract part of a date
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format date as a UNIX timestamp
GET_FORMAT()	Returns a date format string
HOUR()	Extract the hour
LAST_DAY	Returns the last day of the month for the argument
LOCALTIME(), LOCALTIME</td>	Synonym for NOW()</td> </tr>
MAKEDATE()</td>	Create a date from the year and day of year</td> </tr>
MAKETIME</td>	MAKETIME()</td> </tr>
MICROSECOND()</td>	Returns the microseconds from argument</td> </tr>
MINUTE()</td>	Returns the minute from the argument</td> </tr>
MONTH()</td>	Returns the month from the date passed</td> </tr>
MONTHNAME()</td>	Returns the name of the month</td> </tr>
NOW()</td>	Returns the current date and time</td> </tr>
PERIOD_ADD()</td>	Add a period to a year-month</td> </tr>
<PERIOD_DIFF()</td>	Returns the number of months between periods</td> </tr>
QUARTER()</td>	Returns the quarter from a date argument</td> </tr>
SEC_TO_TIME()</td>	Converts seconds to 'HH:MM:SS' format</td> </tr>
SECOND()</td>	Returns the second (0-59)</td> </tr>
STR_TO_DATE()</td>	Convert a string to a date</td> </tr>
SUBTIME()</td>	Subtract times</td> </tr>
SYSDATE()</td>	Returns the time at which the function executes</td> </tr>
TIME_FORMAT()</td>	Format as time</td> </tr>
TIME_TO_SEC()</td>	Returns the argument converted to seconds</td> </tr>
TIME()</td>	Extract the time portion of the expression passed</td> </tr>
TIMEDIFF()</td>	Subtract time</td> </tr>
TIMESTAMP()</td>	With a single argument, this function returns the date or datetime expression. With two arguments, the sum of the arguments</td> </tr>
TIMESTAMPADD()</td>	Add an interval to a datetime expression</td> </tr>
TIMESTAMPDIFF()</td>	Subtract an interval from a datetime expression</td> </tr>
TO_DAYS()</td>	Returns the date argument converted to days</td> </tr>
<UNIX_TIMESTAMP()</td>	Returns a UNIX timestamp</td> </tr>
UTC_DATE()</td>	Returns the current UTC date</td> </tr>
UTC_TIME()</td>	Returns the current UTC time</td> </tr>
UTC_TIMESTAMP()</td>	Returns the current UTC date and time</td> </tr>
WEEK()</td>	Returns the week number</td> </tr>
<WEEKDAY()</td>	Returns the weekday index</td> </tr>
WEEKOFYEAR()</td>	Returns the calendar week of the date (1-53)</td> </tr>
YEAR()</td>	Returns the year</td> </tr>
YEARWEEK()</td>	Returns the year and week</td> </tr> </table>