Mastering Advanced SQL Techniques with MySQL/

...

/

Date and Time

Date and Time

Discover the data types provided by MySQL for date and time.

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:

Press + to interact
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:

Press + to interact
-- 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 ...

Create a free account to view this lesson.

By signing up, you agree to Educative's Terms of Service and Privacy Policy