DATE, DATETIME and TIMESTAMP
Start your free 7-days trial now!
DATE
MySQL's DATE type is to be used when we do not need information about the time. It has the following syntax:
'YYYY-MM-DD'
The supported range is '1000-01-01' to '9999-12-31'.
We can wrap our dates with single quotes as if they were a string '2016-01-01'. MySQL will automatically parse them as a DATE data type, given that you specified the column data type as DATE when you first created your table.
To create a table called products with a DATE type column created_at:
CREATE TABLE products ( id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(30) NOT NULL, price INT NOT NULL, created_at DATE NOT NULL, PRIMARY KEY (id));
To insert two values into the products table:
INSERT INTO products (name, price, created_at) VALUES ('apple', 20, '2018-01-01'), ('banana', 30, '2016-01-01');
SELECT * FROM products;
+----+--------+-------+------------+| id | name | price | created_at |+----+--------+-------+------------+| 1 | apple | 20 | 2018-01-01 || 2 | banana | 30 | 2016-01-01 |+----+--------+-------+------------+
DATETIME
MySQL's DATETIME type is used for values with both date and time parts. It has the following syntax:
'YYYY-MM-DD hh:mm:ss'
The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
You can learn more about the differences between DATETIME and TIMESTAMP here.
TIMESTAMP
MySQL's TIMESTAMP type is used for values with both date and time parts. It has the following syntax:
'YYYY-MM-DD hh:mm:ss'
When you insert a TIMESTAMP value into a table, MySQL automatically converts it to UTC time and stores it. When you retrieve the TIMESTAMP it is automatically converted back to your connection time zone.
The supported range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
You can learn more about the differences between DATETIME and TIMESTAMP here.
Fractional seconds
DATETIME and TIMESTAMP values can store a fractional seconds portion for microseconds (up to 6 digits precision):
'hh:mm:ss.ffffff'
Note that the delimiter between seconds and microseconds must be a dot (.)
For the fractional seconds to be stored, the column must be defined to accept fractional seconds up to specific precision. The general syntax is as follows:
column_name type_name(fsp);
To create a table with columns that can hold fractional seconds up to a specified precision:
CREATE TABLE sample_table (column1 TIME(3), column2 DATETIME(6), column3 TIMESTAMP(1));
