In addition to strings and numbers, databases often store date and/or time values, such as a user's birth date, an employee's hiring date, dates of future events, or the date and time a specific row is created or modified in a table.
This category of data is known as temporal data, and each database engine has default storage formats and data types for managing them. The following table displays the data types supported by the MySQL database server for handling dates and times:
| Type | Default format | Allowable values |
|---|---|---|
DATE |
YYYY-MM-DD |
1000-01-01 to 9999-12-31 |
TIME |
HH:MM:SS or HHH:MM:SS |
-838:59:59 to 838:59:59 |
DATETIME |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 to 9999-12-31 23:59:59 |
TIMESTAMP |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:00 to 2037-12-31 23:59:59 |
YEAR |
YYYY |
1901 to 2155 |
DATE values are formatted as YYYY-MM-DD, where YYYY represents the full year (4 digits), and MM and DD represent the month and day parts of the date, respectively (2 digits with leading zeros). Similarly, TIME values typically follow the format HH:MM:SS, where HH, MM, and SS represent the hours, minutes, and seconds parts of the time, respectively.
The following statement illustrates how to insert a date value into a database table:
INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);Note: In MySQL, the hours part of TIME values can exceed 24 hours because MySQL treats them as elapsed time. This means the TIME data type can represent not only a time of day (which must be less than 24 hours), but also a time interval between two events that may be greater than 24 hours or even negative.
When managing a large application's database, it's common to store the creation time or last modification time of records. For instance, you might store the date and time when a user signs up or updates their password.
In MySQL, you can utilize the NOW() function to insert the current timestamp, like this:
-- Syntax for MySQL Database
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());If you prefer not to manually insert the current date and time, you can utilize the auto-initialization and auto-update features of the TIMESTAMP and DATETIME data types.
To enable automatic properties, include the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions, like this:
-- Syntax for MySQL Database
CREATE TABLE users (
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE,
birth_date DATE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Note: Automatic initialization and updating for the DATETIME data type is supported only in MySQL 5.6.5 or later versions. If you're using an older version, consider using the TIMESTAMP data type instead.
There are situations where you may only need to retrieve a specific part of a date or time value. In MySQL, you can use functions designed for this purpose, such as YEAR(), MONTH(), DAYOFMONTH(), MONTHNAME(), DAYNAME(), HOUR(), MINUTE(), SECOND(), etc.
For example, the following SQL statement extracts the year part from the birth_date column values. If a user's birth_date is 1987-01-14, YEAR(birth_date) will return 1987:
Similarly, you can use the DAYOFMONTH() function to retrieve the day of the month. For example, if a user's birth_date is 1986-10-06, DAYOFMONTH(birth_date) will return 6.
If you prefer a more descriptive and readable date format in your result set, you can utilize the DATE_FORMAT() and TIME_FORMAT() functions to reformat existing date and time values.
The following SQL statement formats the values in the birth_date column of the users table into a more readable format, transforming the value 1987-01-14 into January 14, 1987: