How to format date/time values in MySQL 8

Overview

Working with dates and times is a common task in database management, and knowing how to format these values in MySQL can significantly ease the interaction with temporal data. MySQL 8 offers a variety of functions that we’ll explore to help you handle date and time values more efficiently and customize their presentation to suit your needs.

Before diving into formatting functions, it’s important to understand MySQL’s basic date and time data types: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Proper usage of these types is essential for the accurate representation and manipulation of temporal data.

Basic Formatting with DATE_FORMAT()

The DATE_FORMAT() function allows you to format a date value based on a specifier that defines how the output should look. Here are some examples starting with the most basic one:

SELECT DATE_FORMAT(NOW(), '%W %M %e %Y') AS formatted_date;
-- Output: 'Tuesday March 23 2023'

This query uses placeholders within a quoting string, %W for the day of the week, %M for the month name, %e for the day of the month, and %Y for the year, to format the current date and time provided by the NOW() function.

Time Formatting

Similarly, time values can also be formatted in MySQL using the TIME_FORMAT() function. Consider this example:

SELECT TIME_FORMAT('17:45:30', '%h:%i %p') AS formatted_time;
-- Output: '05:45 PM'

In this example, %h formats the hour in 12-hour format, %i is for minutes and %p shows the AM/PM designation.

Using STR_TO_DATE() for Input Formatting

Just as you may want to format a date for output, you might also need to parse a date string for storage in MySQL. The STR_TO_DATE() function converts a string to a date-time value based on the format string specified:

SELECT STR_TO_DATE('23-03-2023', '%d-%m-%Y') AS formatted_date;
-- Output: '2023-03-23'

This inverse approach is essential when you are inputting date strings and need to ensure they are stored correctly in the database.

Advanced Formatting

MySQL allows for a large set of formatting specifiers. Here are some advanced examples:

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i:%s') AS formatted_datetime;
-- Output: '23/03/2023 14:45:30'

This formats the date and time into a more comprehensive standard format, including hours, minutes, and seconds.

Combining Date and Time

To merge a separate date and time into a datetime, MySQL offers the CONCAT() function:

SELECT CONCAT(CURDATE(), ' ', CURTIME()) AS datetime;
-- Output: '2023-03-23 14:45:30'

Though this doesn’t directly format the date, it provides a means of creating a DATETIME value from separate date and time values.

Conditional Formatting with CASE Statements

You can implement conditional logic within your format specifiers using CASE statements. This allows you to display different formats based on specific criteria within your data:

SELECT CASE
    WHEN DAYOFWEEK(NOW()) = 1 THEN DATE_FORMAT(NOW(), '%W')
    ELSE DATE_FORMAT(NOW(), '%W %d %M %Y')
END AS conditional_format;
-- Output varies: 'Sunday' or 'Tuesday 23 March 2023'

This example changes the output based on whether the current day is Sunday.

Locale-Specific Formatting

For locale-specific date and time formats, MySQL 8 introduces the lc_time_names system variable:

SET lc_time_names = 'es_ES';
SELECT DATE_FORMAT(NOW(), '%W, %d %M %Y') AS formatted_date;
-- Output: 'martes, 23 marzo 2023'

Setting the lc_time_names variable allows date and time values to be output in a specified locale, which can be important for international applications.

Extracting Date and Time Components

Formatting often involves extracting specific components of a date or time:

SELECT EXTRACT(YEAR_MONTH FROM NOW()) AS year_month;
-- Output: '202303'

The EXTRACT() function is an alternative way to fetch parts of a date or time, which you can further format based on your requirements.

Using Format Specifiers and Functions in WHERE Clauses

Formatting isn’t just for output. You may also need to use it within WHERE clauses to filter data:

SELECT * FROM events WHERE DATE_FORMAT(event_date, '%Y-%m') = '2023-03';

By formatting event_date in the YYYY-MM format, you can extract records from a specific month and year.

Conclusion

MySQL 8 provides flexible and powerful tools for formatting date and time values. By mixing and matching a host of built-in functions and specifiers, you can tailor date and time data to fit any scenario, from storage to report generation. Understanding and employing these tools will improve both your database design and user experience.