Mastering Advanced SQL Techniques with MySQL/

...

/

Accessing Date/Time Values

Accessing Date/Time Values

Learn the ways for accessing date and time values in MySQL.

We'll cover the following...

MySQL allows us to create temporal values through literal strings or one of the various functions readily available, e.g., CURTIME(), MAKEDATE(), or NOW(). However, the creation is only one part of working with temporal values. Consequentially, we want to access the created values as easily as possible. For demonstration purposes, we want to refer to the table from our running example:

MySQL
DROP TABLE IF EXISTS date_and_time;
-- Generate a table that records temporal values.
CREATE TEMPORARY TABLE date_and_time
(
    a_date      DATE      DEFAULT (CURRENT_DATE),
    a_time      TIME      DEFAULT (CURRENT_TIME),
    a_datetime  DATETIME  DEFAULT NOW(),
    a_timestamp TIMESTAMP DEFAULT NOW(),
    a_year      YEAR      DEFAULT (YEAR(CURRENT_DATE))
);

As we can observe from the SQL script above, 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. For each data type, we also specify its corresponding default value through functions that retrieve the current date, time, or year.

Access date values

...