SQL Date and Time

In this tutorial, you will learn how to use date and time in SQL.

Date and time processing

The format of the DATE value is YYYY-MM-DD, where YYYY represents the year (4digits), while MM and DD represent the month and day parts of the date (two leading zeros). The time value is usually in HH:MM:SS format, where HH, MM, and SS represent the hour, minute, and second parts of the time, respectively.

The following statement demonstrates how to insert date values into a database table:

INSERT　INTO　employees　(emp_name,　hire_date,　salary)
VALUES　('Adam　Smith',　'2015-06-24',　4500);

Track the creation or modification time of rows

When using the database of a large application, it is usually necessary to store the record creation time or the last modification time in the database, for example, the date and time of user registration or the last time the user updated the password.

In MySQL, you can use the NOW() function to insert the current timestamp, as shown below:

--　Syntax of MySQL database
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());

However, if you do not want to manually insert the current date and time, you can simply use the automatic initialization and automatic update properties of the TIMESTAMP and DATETIME data types.

To assign automatic properties, specify the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in the column definition as follows:

--　Syntax of 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
);

Extracting a part of the date or time

In some cases, you may only want to get a part of the date or time. In MySQL, you can use specially designed functions for extracting parts of time values, such as YEAR(), MONTH(), DAYOFMONTH(), MONTHNAME(), DAYNAME(), HOUR(), MINUTE(), SECOND(), etc.

The following SQL statement extracts the year part of the birth_date column value, for example, if any user's birthday is1987-01-14then YEAR(birth_date) will return1987.

mysql> SELECT name, YEAR(birth_date) FROM users;

Similarly, you can use the DAYOFMONTH() function to get a specific day of the month, for example, if any user's birth_date is1986-10-06If so, DAYOFMONTH(birth_date) will return6.

mysql> SELECT name, DAYOFMONTH(birth_date) FROM users;

Format Date or Time

If you want to use a more descriptive and readable date format in the result set, you can reformat the existing date and time values with the DATE_FORMAT() and TIME_FORMAT() functions.

The following SQL statement will set the format in a more readable format.usersin the tablebirth_dateThe format of the column value, for example1987Year1Month14From day to1987Year1Month14The value of the day.

mysql> SELECT name, DATE_FORMAT(birth_date, '%M %e, %Y') FROM users;