Practical MySQL & MariaDB

MySQL DATE_FORMAT Explained: Complete Guide to Formatting Dates and Times

Functions
目次

1. Before Learning Date Formatting in MySQL: Basic Concepts

Managing date data in a database plays a critical role in any system. In MySQL, accurate data storage and proper formatting are essential. Here, we introduce the fundamental concepts of date storage formats and display formats, and explain the DATE_FORMAT function, which is useful for formatting data.

1.1 MySQL Date Types and Their Characteristics

MySQL provides several data types for handling dates and times. By understanding the purpose of each type, you can select the most appropriate one and optimize database performance.

  • DATE Type
    Stores dates in the “YYYY-MM-DD” format. It is suitable for simple date data that does not require time information (e.g., birthdays or event dates). Example: 2024-10-19
  • DATETIME Type
    Stores both date and time. The format is “YYYY-MM-DD HH:MM:SS” and is useful for managing logs and records that include time information. Example: 2024-10-19 15:30:45
  • TIMESTAMP Type
    Stored as a UNIX timestamp (number of seconds since January 1, 1970). It is often used in systems that span multiple time zones. You can use CURRENT_TIMESTAMP to retrieve the current timestamp.

Choosing the appropriate date type based on your application requirements is important. This improves both data accuracy and query efficiency.

1.2 The Difference Between Storing and Displaying Dates

When storing dates in MySQL, the database saves them in a standard format. However, it is often necessary to display them in a user-friendly format. This is where the DATE_FORMAT function is used. With this function, you can output stored data in a customized format.

2. Basic Usage of the DATE_FORMAT Function

The DATE_FORMAT function is used to format stored dates into a specified format. By using flexible format specifiers, you can customize various display styles.

2.1 Syntax of the DATE_FORMAT Function

DATE_FORMAT(date, format)
  • date: The date value to format (DATE, DATETIME, TIMESTAMP, etc.).
  • format: A string specifying the output format. Uses format specifiers prefixed with %.

For example, the following query converts a date to the “YYYY/MM/DD” format:

SELECT DATE_FORMAT('2024-10-19', '%Y/%m/%d');

Result:

2024/10/19

2.2 Details of Format Specifiers

The DATE_FORMAT function allows flexible customization of date and time using various format specifiers. Below are some commonly used specifiers:

  • %Y: 4-digit year (e.g., 2024)
  • %m: 2-digit month (01–12)
  • %d: 2-digit day (01–31)
  • %W: Weekday name (e.g., Saturday)
  • %H: Hour in 24-hour format (00–23)
  • %i: Minutes (00–59)
  • %s: Seconds (00–59)

For example, the following query displays the weekday along with the year, month, and day:

SELECT DATE_FORMAT('2024-10-19', '%W, %Y-%m-%d');

Result:

Saturday, 2024-10-19

2.3 Custom Text-Based Date Output

You can also use DATE_FORMAT to produce text-based date formats. The following query outputs the date in a long-form style such as “October 19, 2024”.

SELECT DATE_FORMAT('2024-10-19', '%M %d, %Y');

Result:

October 19, 2024

This format is commonly used in reports, invoices, and official documents in English-speaking environments.

3. Advanced DATE_FORMAT Techniques

The DATE_FORMAT function becomes even more powerful when combined with other MySQL functions. Here, we introduce techniques useful for date manipulation.

3.1 Combining with Other Date Functions

The DATE_FORMAT function can be combined with functions such as DATE_ADD and DATE_SUB to perform dynamic date operations.

Example: Add One Month to a Date and Format It
SELECT DATE_FORMAT(DATE_ADD('2024-10-19', INTERVAL 1 MONTH), '%Y-%m-%d');

Result:

2024-11-19

This allows you to perform date calculations and format the results, which is useful for generating dynamic reports and processing data updates.

3.2 Using STR_TO_DATE Together

The STR_TO_DATE function converts a string into a date type. When combined with DATE_FORMAT, you can accurately handle string data as date values.

Example: Convert a String to a Date and Apply Formatting
SELECT DATE_FORMAT(STR_TO_DATE('October 19, 2024', '%M %d, %Y'), '%Y/%m/%d');

Result:

2024/10/19

You can convert complex string data into a date type and then apply the appropriate format afterward.

4. Common Date Format Examples

Here are several date formats commonly used in business and system environments. These examples are practical for real-world applications.

4.1 Slash-Separated Date

SELECT DATE_FORMAT('2024-10-19', '%Y/%m/%d');

Result:

2024/10/19

The slash-separated format is widely used in web forms and user interfaces.

4.2 ISO 8601 Format

SELECT DATE_FORMAT('2024-10-19', '%Y-%m-%d');

Result:

2024-10-19

ISO 8601 is an international standard and is suitable for data exchange between systems.

4.3 Displaying Weekday and Month Name

SELECT DATE_FORMAT('2024-10-19', '%M %W');

Result:

October Saturday

This format is suitable for event schedules and calendar displays.

5. Summary

The DATE_FORMAT function is a powerful tool for flexibly handling date formatting in MySQL. From basic formatting to advanced usage combined with other functions, it can be applied in many different scenarios.

By integrating it with functions such as STR_TO_DATE and DATE_ADD, you can achieve even more flexible data processing. Use the techniques introduced in this article to efficiently manipulate date data and optimize data handling in your systems and applications.

Continue leveraging MySQL features to improve database operation efficiency and develop more sophisticated systems.