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:

```sql
INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);
```
<div class="callout callout-info mb-3">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.</div>

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

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

```sql
-- 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
);
```
<div class="callout callout-info mb-3">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.</div>

## 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.

```sql
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.

```sql
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.

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