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