Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Creating Date/Time Values

Explore how to create temporal values in MySQL by using date, time, datetime, timestamp, and year data types. Learn to generate values with literal strings, built-in functions like CURDATE, NOW, and UTC_TIMESTAMP, and how to handle time zones effectively for automated and accurate date/time data management.

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:

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