Date and Time data types

MySQL has several data types for working with dates and times: DATE, TIME, DATETIME and TIMESTAMP.

Difference between TIMESTAMP and DATETIME

The MySQL DATETIME and TIMESTAMP data types are similar in that they both store dates and times. But there are a number of significant differences between them that determine which of these types is better to use where.

DATETIME

Stores values in the range 1000-01-01 00:00:00 to 9999-12-31 23:59:59 and occupies 8 bytes. This data type does not depend on the time zone, installed in MySQL. It is always displayed exactly like this, in in which it was installed and in which it is stored in the database. That is, at change the time zone, the time display will not change.

MySQL
CREATE TABLE datetime_table (datetime_field DATETIME);
SET @@session.time_zone="+00:00"; -- reset timezone in MYSQL
INSERT INTO datetime_table VALUES("2022-06-16 16:37:23");
SET @@session.time_zone="+03:00"; -- change timezone in MySQL
SELECT * FROM datetime_table;

TIMESTAMP

Stores how many seconds have passed since 1970-01-01 00:00:00 in the zero time zone and occupies 4 bytes. When making selections, it is displayed based on the current time zone. The time zone can be set in the operating room settings. system where MySQL is running, in the global MySQL settings or in a specific session. In the database, when a TIMESTAMP record is created, the value is stored in the zero time zone.

MySQL
CREATE TABLE timestamp_table (timestamp_field TIMESTAMP);
SET @@session.time_zone="+00:00"; -- reset timezone in MYSQL
INSERT INTO timestamp_table VALUES("2022-06-16 16:37:23");
SET @@session.time_zone="+03:00"; -- change timezone in MySQL
SELECT * FROM timestamp_table;

It is also worth remembering that TIMESTAMP is essentially limited in the range of possible values from 1970-01-01 00:00:01 to 2038-01-19 03:14:07, which limits its use. So, this data type is not suitable for storing users dates of birth.

Ways to set values

The DATETIME, DATE, and TIMESTAMP values can be set in one of the following ways:

• As a string in the format YYYY-MM-DD HH:MM:SS or in the format YY-MM-DD HH:MM:SS for date and time
• As a string in YYYY-MM-DD format or in YY-MM-DD format to indicate dates only

When writing a date, you can use any punctuation mark in as a separator between parts of sections of a date or time. Also it is possible to set the date without a separator character at all, together.

MySQL
CREATE TABLE date_table (datetime TIMESTAMP);
INSERT INTO date_table VALUES("2022-06-16 16:37:23");
INSERT INTO date_table VALUES("22.05.31 8+15+04");
INSERT INTO date_table VALUES("2014/02/22 16*37*22");
INSERT INTO date_table VALUES("20220616163723");
INSERT INTO date_table VALUES("2021-02-12");
SELECT * FROM date_table;