DATE and TIME data types in MySQL 8: Explained with examples

Introduction

DATE and TIME are foundational data types in database management systems like MySQL 8, essential for dealing with temporal data. Understanding how to manipulate and store these data types is critical for many applications, from scheduling systems to historical record-keeping. In this article, we’ll delve into the nuances of DATE and TIME data types in MySQL 8, exploring their functionalities with examples to elucidate their practical use.

Understanding DATE and TIME Data Types

The DATE data type is used for values containing the date in the format YYYY-MM-DD, whereas TIME data type is used for values that represent the time of day in the format HH:MM:SS. A distinct feature of MySQL 8 is its extensive range for date and time types, allowing dates to be stored ranging from the year 1000 to 9999, and time values up to 838 hours.

-- Example 1: Creating a table with DATE and TIME columns
CREATE TABLE schedule (
  event_date DATE,
  event_time TIME
);

In the above example, a table named ‘schedule’ is created with columns for event date and time.

Inserting Data into DATE and TIME Columns

Once you have a table with DATE and TIME columns, the next step is to insert data into them. Here’s how:

-- Example 2: Inserting data into the table
INSERT INTO schedule (event_date, event_time) VALUES
('2023-04-12', '14:30:00'),
('2023-04-13', '15:30:00');

This inserts two rows into the ‘schedule’ table with specific dates and times for events.

Retrieving Data

Retrieving DATE and TIME data from your database is straightforward, but often you will want to manipulate this data:

-- Example 3: Retrieving only the month and day from the DATE column
SELECT MONTH(event_date), DAY(event_date) FROM schedule;

-- Output
+------------------+-----------------+
| MONTH(event_date) | DAY(event_date) |
+------------------+-----------------+
|                4 |              12 |
|                4 |              13 |
+------------------+-----------------+

Here, you’ll get the month and day for each event gathered from the ‘event_date’ column.

Working with Time Zones

One advanced feature in MySQL 8 is the handling of time zones. You are capable of setting time zone on a per-connection basis:

-- Example 4: Setting the time zone for a session
SET time_zone = '+00:00'; -- Coordinated Universal Time (UTC)
SET time_zone = 'SYSTEM'; -- The system's time zone
SET time_zone = 'America/New_York'; -- Eastern Time

In these examples, the session’s time_zone setting is changed.

Formatting Dates and Times

The DATE_FORMAT() function provides an effective way to format a DATE or DATETIME value into a specific string format:

-- Example 5: Formatting DATE and TIME
SELECT DATE_FORMAT(event_date, '%W, %M %e, %Y') as formatted_date,
  TIME_FORMAT(event_time, '%h:%i %p') as formatted_time
FROM schedule;

-- Output
+-----------------------------+---------------+
| formatted_date              | formatted_time|
+-----------------------------+---------------+
| Wednesday, April 12, 2023   | 02:30 PM      |
| Thursday, April 13, 2023    | 03:30 PM      |
+-----------------------------+---------------+

With DATE_FORMAT() and TIME_FORMAT(), the date and time are presented in a more human-readable form.

Operations on DATE and TIME

MySQL provides several functions to perform arithmetic on DATE and TIME values. The following example demonstrates adding intervals to dates:

-- Example 6: Adding an interval to a DATE
UPDATE schedule SET event_date = DATE_ADD(event_date, INTERVAL 1 DAY);

-- Betting the current value after update
SELECT event_date FROM schedule;

-- Output
+------------+
| event_date |
+------------+
| 2023-04-13 |
| 2023-04-14 |
+------------+

The DATE_ADD function advances the date by the specified interval.

Complex Queries with DATE and TIME

DATE and TIME data types are frequently used in more complex queries, such as in a JOIN where you might need to compare dates from different tables:

-- Example 7: Complex join query using DATE
SELECT A.event_name, B.event_time
FROM schedule_events AS A
JOIN schedule_times AS B ON A.event_date = B.event_date;

This pseudo-query demonstrates how you might join two tables based on their date columns to gather cohesive event information.

Conclusion

Understanding MySQL DATE and TIME data types is paramount for any developer working with temporal data. Through INSERT, UPDATE, formatting, and more, you can precisely control how your application interacts with time. With these examples and explanations, you’re well-equipped to harness the power of MySQL’s robust temporal data handling capabilities in your next project.