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