Search⌘ K
AI Features
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Accessing Date/Time Values

Explore how to access and manipulate MySQL date and time values using various functions. Learn to extract parts of dates and times, format temporal data, and handle combined date-time values for precise data queries and presentation.

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

...