Practical MySQL & MariaDB

MySQL DATE_FORMAT Explained: Format Dates and Times with Examples

Functions
目次

1. Introduction

In MySQL databases, you will frequently handle date and time data. Dates stored in a database are saved in standard formats, but when displaying them to users, you often want to reformat them for better readability. That’s where the DATE_FORMAT function becomes useful. In this article, we will introduce how to use the DATE_FORMAT function and various formatting options, and explain practical ways to apply it through real examples.

2. Overview of the DATE_FORMAT Function

2.1 What Is the DATE_FORMAT Function?

The DATE_FORMAT function is a MySQL function used to convert date data into a specified format. It is used when you want to output dates in an arbitrary format rather than the default YYYY-MM-DD format or standard datetime format. For example, if you want to display a date to users in a format like “Sep 16, 2024”, this function is very helpful.

2.2 Basic Syntax

The basic syntax of the DATE_FORMAT function is as follows.

DATE_FORMAT(date, format)
  • date: The date data you want to format.
  • format: A string that specifies the output date format.

Let’s look at a specific example:

SELECT DATE_FORMAT('2024-09-16', '%Y-%m-%d') AS formatted_date;

This query converts the date ‘2024-09-16’ into the format “2024-09-16” and outputs it.

3. Date Format Parameters

3.1 List of Format Specifiers

The DATE_FORMAT function supports many format specifiers. Below is a list of commonly used ones:

  • %Y: 4-digit year (e.g., 2024)
  • %y: 2-digit year (e.g., 24)
  • %m: 2-digit month (01 to 12)
  • %c: Month (1 to 12)
  • %d: 2-digit day (01 to 31)
  • %e: Day (1 to 31)
  • %H: Hour in 24-hour format (00 to 23)
  • %h or %I: Hour in 12-hour format (01 to 12)
  • %i: Minutes (00 to 59)
  • %s: Seconds (00 to 59)
  • %p: AM or PM

3.2 Real Examples

Let’s see concrete examples of how these specifiers affect the output.

SELECT 
    DATE_FORMAT('2024-09-16 14:35:59', '%Y-%m-%d %H:%i:%s') AS full_format,
    DATE_FORMAT('2024-09-16 14:35:59', '%b %d, %Y') AS us_format,
    DATE_FORMAT('2024-09-16 14:35:59', '%d/%m/%Y') AS european_format,
    DATE_FORMAT('2024-09-16 14:35:59', '%h:%i %p') AS twelve_hour_format;

The output of this query will look like this:

  • full_format: 2024-09-16 14:35:59
  • us_format: Sep 16, 2024
  • european_format: 16/09/2024
  • twelve_hour_format: 02:35 PM

4. Practical Use Cases

4.1 Scenario 1: Report Generation

For example, when generating monthly reports in a company, you may want to display dates in a “YYYY-MM” style format. The following query formats report dates for that purpose.

SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS report_month,
    SUM(sales) AS total_sales
FROM sales_data
GROUP BY report_month;

This query outputs the total monthly sales from the sales data in a format like “2024-09”.

4.2 Scenario 2: User Interface

DATE_FORMAT is also useful when you want to display dates clearly to users in a web application. For example, you can use it like this when showing the last login date on a user profile page.

SELECT 
    user_name,
    DATE_FORMAT(last_login, '%Y-%m-%d %H:%i') AS last_login_formatted
FROM users;

This will display the user’s last login date and time like “2024-09-16 14:35”.

4.3 Scenario 3: Query Optimization

In some cases, DATE_FORMAT can also be useful for database query optimization. For example, you can use formatting when extracting data within a specific date range.

SELECT 
    *
FROM transactions
WHERE DATE_FORMAT(transaction_date, '%Y-%m') = '2024-09';

This query extracts all transactions that occurred in September 2024.

5. Notes and Best Practices for DATE_FORMAT

5.1 Performance Considerations

If you use DATE_FORMAT frequently, it may affect performance. In particular, repeatedly applying it to large datasets can increase processing time. If needed, consider storing pre-formatted dates in advance or formatting them at the application level.

5.2 Localization

When building a multilingual system using DATE_FORMAT, you must pay attention to localization. Since date display formats vary by country and region, you may need to dynamically change formats based on the user’s locale.

5.3 Consistent Formatting

Using consistent date formats across an entire system is essential for improving the user experience. For example, by using the same format in input forms, display sections, and reports, you can avoid confusing users.

6. Summary

The DATE_FORMAT function is a powerful tool for formatting date data flexibly in MySQL. In this article, we covered basic usage, practical examples, and important notes and best practices. By using this function effectively, you can display dates in a way that is clearer and more user-friendly. As a next step, we recommend learning more advanced date and time operations.

7. Reference Resources

We hope this article helps you understand and effectively use the DATE_FORMAT function.