The World of MySQL & MariaDB

How to Use DATE_FORMAT in MySQL: Complete Guide to Date and Time Formatting

Functions
目次

1. Before Learning MySQL Date Formatting: The Basics

Managing date data in a database plays a critical role in every system. In MySQL, accurate storage and proper formatting are essential. Here, we’ll introduce the basics of how dates are stored and displayed, and explain the DATE_FORMAT function, which is useful for formatting date outputs.

1.1 MySQL Date Types and Their Features

MySQL provides multiple data types for handling dates and times. Understanding their use cases helps you choose the right type and optimize database performance.

  • DATE Type
    Stores dates in the format YYYY-MM-DD. Suitable for simple date-only data (e.g., birthdays or event dates). Example: 2024-10-19
  • DATETIME Type
    Stores both date and time in the format YYYY-MM-DD HH:MM:SS. Useful for logs or records where time is required. Example: 2024-10-19 15:30:45
  • TIMESTAMP Type
    Stores data as a UNIX timestamp (the number of seconds since January 1, 1970) and is often used in systems that span different time zones. You can retrieve the current timestamp with CURRENT_TIMESTAMP.

Choosing the right date type based on your application’s requirements is crucial for accuracy and query efficiency.

1.2 Difference Between Storage and Display

When MySQL stores dates, it uses a standard format. However, users often need dates displayed in a more readable or localized way. That’s where the DATE_FORMAT function comes in—it lets you output stored data in a customized format.

In the next section, we’ll look at how DATE_FORMAT works and practical ways to use it.

2. Basic Usage of the DATE_FORMAT Function

The DATE_FORMAT function is used to format stored dates into the desired output format. By using flexible format specifiers, you can display dates in a variety of styles.

2.1 Syntax of DATE_FORMAT

DATE_FORMAT(date, format)
  • date: The date value you want to format (DATE, DATETIME, TIMESTAMP, etc.).
  • format: A string that specifies the output format, using format specifiers with the % symbol.

For example, this query converts a date into the YYYY/MM/DD format:

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

Result:

2024/10/19

2.2 Common Format Specifiers

The DATE_FORMAT function supports many format specifiers for customizing date and time outputs. Here are some common ones:

  • %Y: Four-digit year (e.g., 2024)
  • %m: Two-digit month (01–12)
  • %d: Two-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 outputs the weekday along with year, month, and day:

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

Result:

Saturday, 2024-10-19

2.3 Output in Japanese Format

You can also use DATE_FORMAT for localized outputs. For instance, this query returns the date in the Japanese format YYYY年MM月DD日:

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

Result:

2024年10月19日

This format is commonly used in Japanese reports, invoices, and official documents.

3. Advanced DATE_FORMAT Techniques

DATE_FORMAT becomes even more powerful when combined with other MySQL functions. Let’s look at some useful techniques for handling date operations.

3.1 Combining with Other Date Functions

You can combine DATE_FORMAT with functions like DATE_ADD and DATE_SUB for dynamic date calculations.

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 is useful for generating dynamic reports and processing date-based updates.

3.2 Using with STR_TO_DATE

The STR_TO_DATE function converts a string into a date type. Combined with DATE_FORMAT, you can parse strings into valid date data and then format them as needed.

Example: Convert a String into a Date and Apply Formatting
SELECT DATE_FORMAT(STR_TO_DATE('2024年10月19日', '%Y年%c月%e日'), '%Y/%m/%d');

Result:

2024/10/19

This allows you to handle complex string-based dates and format them correctly.

4. Common Real-World Date Formats

Here are some frequently used date formats in business and system applications.

4.1 Date with Slashes

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

Result:

2024/10/19

This 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 the international standard and ideal for data exchange between systems.

4.3 Displaying Weekday and Month Name

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

Result:

October Saturday

This is useful for event schedules and calendar views.

5. Conclusion

The DATE_FORMAT function is a powerful tool for flexible date formatting in MySQL. From simple formatting to advanced use cases combined with other functions, it is highly versatile.

By using functions like STR_TO_DATE and DATE_ADD together, you can achieve even more dynamic data processing. Apply the techniques covered in this article to efficiently manage date data and optimize your system or application.

Continue leveraging MySQL’s features to improve database operations and build more refined systems.