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.

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 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:

  • 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:

  • -- 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:

  • -- Syntax for MySQL Database 
  • CREATE TABLE users (
  •     name VARCHAR(50) NOT NULL UNIQUE,
  •     birth_date DATE NOT NULL,
  • );

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;

Your Feedback:

We would love to hear from you, please drop us a line.