Search⌘ K
AI Features
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Date and Time

Explore MySQL's date and time data types to learn how to store and manipulate dates, times, and timestamps effectively. This lesson covers the formats, ranges, and specific use cases for DATE, TIME, DATETIME, TIMESTAMP, and YEAR data types, helping you manage temporal data in databases.

We'll cover the following...

Date and time are an integral part of every database. Even if the data in a database is not related to date and time by topic, SQL tables often include columns with metadata. For example, a table may include metadata like the date and time at which a record was created:

MySQL
DROP TABLE IF EXISTS User;
CREATE TEMPORARY TABLE User
(
    -- ...
    created_at DATETIME DEFAULT NOW()
    -- ...
);

Hence, MySQL provides various data types related to storing date and time, i.e., DATE, TIME, DATETIME, TIMESTAMP, and YEAR. With our running example of a table that stores car models, we already encountered a data type to represent years:

MySQL
-- Generate a temporary table for sample car models.
DROP TABLE IF EXISTS CarModel;
CREATE TEMPORARY TABLE CarModel
(
    id             INT auto_increment,
    name           TEXT DEFAULT NULL,
    `power (kW)`   INT  DEFAULT NULL,
    `release year` YEAR DEFAULT NULL,
    PRIMARY KEY (id)
);

Beyond a car model’s release year, we could also be interested in recording metadata about the car models that our table CarModel stores. Even more so, our car models are assembled in a factory, where the engineers are interested in the state of assembly, i.e., the date and time when certain parts were assembled. Therefore, we need to look into data types that are more fine-grained than YEAR.

The DATE for dates

The data type for representing dates in MySQL is DATE. Values of this data type are represented as 'YYYY-MM-DD'. Working with a table with a column of type DATE, for example, allows us to input a value in the previously mentioned format and various others. 'YYYY-MM-DD' (also without limiters), 'YY-MM-DD' (also without delimiters), YYYYMMDD, and YYMMDD are supported for input parsing.

Previously, using delimiters other than - was permitted, but it is deprecated as of MySQL 8.0.29 and will display a warning. Regardless of the format used, the values permitted for DATEs range from 1000-01-01 to 9999-12-31. Per definition, a DATE is not associated with a particular TIME; for that we need to turn to DATETIME.

The TIME[(fsp)] for time

TIME[(fsp)] is the data type that represents time in MySQL. The format used to represent values of this type is 'hhh:mm:ss[.fraction]' where hhh represents hours, mm represents minutes, and ss represents seconds. TIME[(fsp)] not only represents the hours of the day but can also convey elapsed time and the time between two events. Hence, the hours count has not only two but three digits. [.fraction] represents the optional fractional part of a second and corresponds to a sequence of fsp (fractional seconds precision) digits where fsp can range from 0 (default) to 6. For example, '17:03:46.031' is a valid value for a row’s field with the data type TIME(3). Aside from the format mentioned above, TIME[(fsp)] can be input as 'D hh:mm:ss' (also permitted with fractional part), 'hh:mm:ss' (also permitted without delimiters and with fractional part), 'hh:mm', 'D hh:mm', 'D hh', and 'ss'. Numerically, the formats hhmmss (also permitted with a fractional part), mmss, and ss are also supported. For all these formats, the values permitted range from -838:59:59.000000 to 838:59:59.000000.

The DATETIME[(fsp)] for dates with associated time

The data type representing a date associated with a time of the day in MySQL is DATETIME[(fsp)]. It is not exactly a combination of DATE and TIME but close. The format used to represent DATETIME[(fsp)] is 'YYYY-MM-DD hh:mm:ss[.fraction]' where [.fraction] corresponds to an optional sequence of fsp digits. Hence, fsp can range from 0 (default) through 6. For example, '2022-01-01 00:00:00.01' is a valid value for a column with data type DATETIME(2). For parsing a value of type DATETIME[(fsp)], the format is identical to DATE but includes a time of the day, e.g., YYYYMMDDhhmmss (as a number) is a valid value for DATETIME[(fsp)]. In addition to the whitespace character between the date and time part, the T character is a valid separator. Using delimiters other than : for the time part was previously permitted but is deprecated as of MySQL 8.0.29 and will display a warning. Regardless of the format used, the values permitted for the time of the day range from 00:00:00 to 23:59:59. The values valid for the date part are the same as for DATE. With this knowledge, we are now able to extend our running example of car models with a record of assembled car parts:

MySQL
-- Generate a table for sample car model parts.
DROP TABLE IF EXISTS CarPart;
CREATE TABLE CarPart
(
    model    INT,
    name     TEXT,
    built_at DATETIME DEFAULT NULL,
    PRIMARY KEY (model, name(256)),
    FOREIGN KEY (model) REFERENCES CarModel (id)
);

The table CarPart consists of three columns, namely model, name, and built_at. The first column, model, references the car model that this part belongs to as a foreign key (i.e., FOREIGN KEY (model) REFERENCES CarModel (id)). name records the name of the car part and constitutes the primary key of CarPart together with model (i.e., PRIMARY KEY (model, name(256))). For our example, we consider three parts, namely exterior, interior, and wheels. Finally, the third column, built_at, contains the date and time the corresponding part has been assembled. If that hasn’t happened so far for a part, the column reads NULL. As such, we can record car models and their parts as such:

MySQL
-- Populate the generated table with sample car models.
INSERT INTO CarModel (manufacturer, name, `power (kW)`, `release year`)
VALUES ('Audi', 'A3', 81, 2020),
       ('Audi', 'A4', 100, 2015),
       ('Audi', 'A5', 110, 2016),
       ('Audi', 'A6', 210, 2018),
       ('Audi', 'A7', 150, 2018),
       ('Audi', 'A8', 210, 2017),
       ('Mercedes Benz', 'C63 AMG', 350, 2015),
       ('Mercedes Benz', 'S63 AMG', 450, 2013),
       ('Volkswagen', 'Golf VII', 92, 2012),
       ('Volkswagen', 'Golf VIII', 110, 2019);
-- Populate the generated table with sample car model parts.
INSERT INTO CarPart (model, name, built_at)
VALUES (1, 'exterior', '2022-03-28 06:46:00'),
       (1, 'interior', NULL),
       (1, 'wheels', '2022-03-28 07:56:00'),
       (2, 'exterior', '2022-03-28 21:47:00'),
       (2, 'interior', '2022-03-28 15:20:00'),
       (2, 'wheels', '2022-03-28 12:13:00'),
       (3, 'exterior', '2022-03-27 13:31:00'),
       (3, 'interior', '2022-03-28 08:46:00'),
       (3, 'wheels', '2022-03-28 11:54:00'),
       (4, 'exterior', '2022-03-26 18:04:00'),
       (4, 'interior', NULL),
       (4, 'wheels', NULL),
       (5, 'exterior', '2022-03-28 21:26:00'),
       (5, 'interior', '2022-03-28 15:44:00'),
       (5, 'wheels', '2022-03-28 09:43:00'),
       (6, 'exterior', NULL),
       (6, 'interior', NULL),
       (6, 'wheels', NULL),
       (7, 'exterior', '2022-03-27 15:28:00'),
       (7, 'interior', NULL),
       (7, 'wheels', NULL),
       (8, 'exterior', NULL),
       (8, 'interior', NULL),
       (8, 'wheels', NULL),
       (9, 'exterior', '2022-03-26 13:12:00'),
       (9, 'interior', '2022-03-27 14:51:00'),
       (9, 'wheels', '2022-03-28 13:28:00'),
       (10, 'exterior', '2022-03-24 15:23:00'),
       (10, 'interior', '2022-03-26 15:28:00'),
       (10, 'wheels', '2022-03-28 16:53:00');
TABLE CarModel;
TABLE CarPart;

Notably, we can specify the assembly date and time as discussed in this section.

The TIMESTAMP[(fsp)] for dates with associated time

Similar to DATETIME[(fsp)], TIMESTAMP[(fsp)] is the data type in MySQL to represent a date associated with a time of the day. However, TIMESTAMP[(fsp)] has a more constrained range and stores time with time zone information. The formats used to represent and parse TIMESTAMP[(fsp)] are identical to DATETIME[(fsp)]. However, storing a value of type TIMESTAMP[(fsp)] causes the value to be converted from the server’s (where MySQL is running) time zone to UTC. Similarly, retrieving a value of type TIMESTAMP[(fsp)] causes the value to be converted from UTC to the server’s time zone. As mentioned, the TIMESTAMP[(fsp)] range is constrained from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

The YEAR for years

As discussed in the beginning, MySQL provides the data type YEAR with the display format YYYY to represent a single year. For input parsing of YEAR, 'YYYY', 'YY', and 'Y' are equally valid. In the case of the latter two shortcut formats, the strings '0' through '69' are associated with the years 2000 to 2069, while the strings '70' through '99' are associated with the years 1970 to 1999. Numerically, the same data formats are accepted. In this case, the values from 1 through 69 are associated with the years 2001 to 2069, while the values from 70 to 99 are associated with the years 1970 to 1999. A numerical value 0 is associated with the year 0000. In all cases, the values permitted for YEAR include 0000 and years from 1901 through 2155.

Automatic initialization of date and time

For DATETIME and TIMESTAMP, we can use DEFAULT CURRENT_TIMESTAMP (or alternatives to CURRENT_TIMESTAMP, like NOW() or LOCALTIME) to automatically initialize the corresponding column in the row to the current time:

MySQL
-- Create a temporary table for demonstration purposes.
CREATE TEMPORARY TABLE date_and_time
(
    bar        INT               DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT NOW(),
    updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);
-- Populate the generated table with a default value.
INSERT INTO date_and_time VALUE ();
-- Wait one second before updating the inserted value.
SELECT SLEEP(1);
-- Updating the inserted value with a dummy value.
UPDATE date_and_time
SET bar = 1;
-- Inspect the automatically populated values in `created_at` and `updated_at`.
TABLE date_and_time;

The same is possible for ON UPDATE, setting the corresponding column in the row to the current time as soon as another field is updated.