Mastering Advanced SQL Techniques with MySQL/

...

/

Introduction to Date and Time Functions

Introduction to Date and Time Functions

Learn about functions for date and time in MySQL.

We'll cover the following...

MySQL supports five categories of data types: numeric, date and time, string, spatial, and JSON. For date and time, the database management system provides a variety of data types, i.e., DATE, TIME, DATETIME, TIMESTAMP, and YEAR. The data type representing dates is DATE with values representing 'YYYY-MM-DD'. TIME represents time as 'hhh:mm:ss' where hhh denotes hours, mm represents minutes, and ss stands for seconds. The data type representing a date associated with a time of the day in MySQL is DATETIME. It is not exactly a combination of DATE and TIME but close. The format used to represent DATETIME is 'YYYY-MM-DD hh:mm:ss'. Similar to DATETIME, TIMESTAMP is the data type representing a date associated with a time of the day. However, TIMESTAMP has a more constrained range and stores time with time zone information. Finally, MySQL provides the data type YEAR with the display format YYYY to represent a single year.

We already encountered the SQL function CAST() to cast a value of any type to a value of a specified target data type. With this, CAST() can also modify temporal values. For example, we can convert a DATE to other temporal data types as follows:

MySQL
-- Generate a table with all available temporal data types.
DROP TABLE IF EXISTS date_and_time;
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 (CURRENT_DATE)
);
-- Use the current date and time for each data type.
INSERT INTO date_and_time VALUE ();
-- Convert a `DATE` to the remaining temporal data types:
SELECT CAST(a_date AS TIME), CAST(a_date AS DATETIME), CAST(a_date AS YEAR)
FROM date_and_time;

To convert the data type DATE to other temporal data types, we leverage a temporary table:

  • First, we create a temporary table named date_and_time with columns for different temporal data types (DATE, TIME, ...