SQL Dates and Times

In this tutorial you will learn how to work with dates and times in SQL.

Date and Time Manipulation

Along with strings and numbers, you often need to store date and/or time values in a database, such as an user’s birth date, employee’s hiring date, date of the future events, the date and time a particular row is created or modified in a table, and so on.

This type of data is referred as temporal data and every database engine has a default storage format and data types for storing them. The following table shows the data types supported by the MySQL database server for handling the dates and times.

DATE values have YYYY-MM-DD format, where YYYY represent the full year (4 digits), whereas the MM and DD represents the month and day parts of the date respectively (2 digits with leading zero). Likewise, the TIME values have normally HH:MM:SS format, where HH, MM, and SS represents the hours, minutes, and seconds parts of the time respectively.

The following statement demonstrates how to insert a date value in database table:

Example

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

Note: In MySQL the hours part of the TIME values may be larger, because MySQL treats them as elapsed time. That means the TIME data type can be used not only to represent a time of day (which must be less than 24 hours), but also a time interval between two events which may be greater than 24 hours, or even negative.

Tracking Row Creating or Modification Times

While working with the database of a large application you often need to store record creation time or last modification time in your database, for example, storing the date and time when a user sign up, or when a user last updated his password, etc.

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

Example

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

However, if you don’t want to insert current date and time manually, you can simply use the auto-initialization and auto-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 column definitions, as follow:

Example

-- 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 DATETIME data type is only available in MySQL 5.6.5 or later. If you’re using an older version use the TIMESTAMP instead.

Extracting Parts of Dates or Times

There may be situations where you just want to obtain a part of date or time. In MySQL you can use the functions specifically designed for extracting part of a temporal value, such as YEAR(), MONTH(), DAYOFMONTH(), MONTHNAME(), DAYNAME(), HOUR(), MINUTE(), SECOND(), etc.

The following SQL statement will extract the year part of the birth_date column values, e.g. if the birth_date of any user is 1987-01-14 the YEAR(birth_date) will return 1987.

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

Similarly, you can use the function DAYOFMONTH() to get the day of the month, e.g. if the birth_date of any user is 1986-10-06 the DAYOFMONTH(birth_date) will return 6.

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

Formatting Dates or Times

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

The following SQL statement will format the values of birth_date column of the users table in more readable format, like the value 1987-01-14 to January 14, 1987.

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