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 values have
YYYY-MM-DD format, where
YYYY represent the full year (4 digits), whereas the
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
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
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 (
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
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
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.
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.
Formatting Dates or Times
If you want more descriptive and readable date format in your result set, you can use the
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.