Mastering Advanced SQL Techniques with MySQL/

...

/

Creating Date/Time Values

Creating Date/Time Values

Learn how to create date and time values in MySQL.

We'll cover the following...

MySQL provides five data types for temporal values, i.e., DATE, TIME, DATETIME, TIMESTAMP, and YEAR. To work with these data types, we must first create corresponding values. For that purpose, we work with the following table:

Press + to interact
DROP TABLE IF EXISTS date_and_time;
-- Generate a table that records temporal values.
CREATE TEMPORARY TABLE date_and_time
(
    a_date      DATE,
    a_time      TIME,
    a_datetime  DATETIME,
    a_timestamp TIMESTAMP,
    a_year      YEAR
);

As we can observe from the above SQL script, the table date_and_time contains five columns that represent the five temporal data types of MySQL: a_date for DATE, a_time for TIME, a_datetime for DATETIME, a_timestamp for TIMESTAMP, and a_year for YEAR. Intuitively, we can create a row in date_and_time by specifying the values for the five columns literally. However, this is tedious and may not even work when automated creation of temporal values is necessary.

Create date values

As mentioned before, one common way of creating a date value is using a literal string that ...