MySQL Date and Time Guide: Data Types, Functions, Formatting, and Range Queries

1. Introduction

MySQL is a widely used database management system for building web applications and databases, and handling date data properly is especially important. For example, date-related data appears in many scenarios: managing blog posts, tracking product sales history, and storing user login history. By managing date data correctly, you can process data efficiently and provide accurate information to users.

In this guide, we will comprehensively explain everything from the basic date/time data types and how to use date functions in MySQL to date-based calculations and range queries. This content is designed for beginners to intermediate users, and we will include practical query examples based on real-world use cases—making it useful for day-to-day work.

By reading this article, you will be able to do the following:

• Understand the characteristics of MySQL date/time data types and choose the appropriate type for your data.
• Use date functions to easily retrieve and manipulate the current date, past dates, and future dates.
• Extract data for specific periods by performing date range searches and comparisons.

Now, let’s look at the fundamentals of MySQL dates starting from the next section.

2. Overview of MySQL Date/Time Data Types

When managing dates in MySQL, it’s important to choose the right date/time data type depending on the data and the use case. MySQL provides multiple data types for handling dates and times, each with different characteristics and usage. In this section, we’ll explain the main date/time types and their typical use cases in detail.

DATE

The DATE type stores only the date (year, month, day) and does not include a time component. The supported range is from “1000-01-01” to “9999-12-31,” so it can handle dates from year 1000 through 9999. It’s suitable for date-only information where time is not relevant, such as birthdays or anniversaries.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

The TIME type stores time (hours, minutes, seconds). The supported range is from “-838:59:59” to “838:59:59.” Because it can represent negative times, it can also be used to express time differences. It’s useful for recording working hours or task durations.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

The DATETIME type stores both date and time. The supported range is from “1000-01-01 00:00:00” to “9999-12-31 23:59:59.” It does not depend on time zones, and you can retrieve the stored value exactly as saved. It’s suitable for recording timestamps of past events or future schedules.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

The TIMESTAMP type stores date and time and automatically converts values based on the server time zone. The supported range is from “1970-01-01 00:00:01 UTC” to “2038-01-19 03:14:07 UTC.” It is compatible with Unix epoch time and is suitable for recording timestamps and change history. Also, because TIMESTAMP can set the current time by default, it’s convenient for automatically recording creation and update times.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

The YEAR type stores only the year. The supported range is from “1901” to “2155.” It’s useful when you want to represent a specific year, such as a manufacturing year or a founding year.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Comparison and Use Cases of Each Date/Time Type

Summary

MySQL date/time data types should be chosen based on the characteristics of your data and how you plan to use it. In the next section, we’ll take a closer look at the differences between DATETIME and TIMESTAMP, which are especially easy to confuse.

3. Differences Between DATETIME and TIMESTAMP

When working with dates and times in MySQL, DATETIME and TIMESTAMP are commonly used, but there are important differences between them. Both store year, month, day, hour, minute, and second, but they differ in how they handle time zones and the time range they can store. You should choose based on your use case. In this section, we’ll explain the differences and characteristics of DATETIME and TIMESTAMP in detail.

Characteristics of DATETIME

• Not dependent on time zones: The DATETIME type is not affected by the server time zone setting. You can retrieve the stored value exactly as saved.
• Range: It supports from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
• Use cases: Suitable for data you want to store without relying on any particular time zone, such as historical events or future schedules.

Example: Storing an event date/time

For example, if you want to keep a “universal” date/time value as-is, DATETIME is a good choice. The following example records an event scheduled at a specific date and time.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

In this table, the date/time stored in event_datetime is not affected by time zones, and the value is retrieved exactly as stored.

Characteristics of TIMESTAMP

• Dependent on time zones: The TIMESTAMP type is stored and retrieved based on the server time zone. When moving data between servers in different time zones, conversion will occur accordingly.
• Range: It supports from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (based on the Unix time range).
• Auto-update support: Using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, MySQL can automatically record the current timestamp when inserting or updating data.
• Use cases: Best for automatically tracking creation or update time, such as when you want a timestamp to change whenever a record is updated.

Example: Storing created and updated times for posts

This example uses the auto-update feature of TIMESTAMP to record creation and update times for blog posts.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

With this setting, created_at is recorded when the post is first created, and updated_at is automatically updated every time the post is edited.

Comparison Table: DATETIME vs TIMESTAMP

How to Choose

• Choose DATETIME if you don’t want time zone effects: For example, if you want to store an event time as a fixed local time for a specific country/region, DATETIME can be appropriate.
• Choose TIMESTAMP if you need auto-update or time zone handling: For example, if you want to automatically record when database rows are updated, TIMESTAMP is convenient.

Summary

DATETIME and TIMESTAMP have different characteristics. By choosing the right one for your purpose, you can manage date/time data more efficiently. In the next section, we’ll look at the essential date functions in MySQL.

4. MySQL Date/Time Functions Basics

MySQL provides many functions for working with dates and times—from retrieving the current date/time to adding or subtracting intervals. These functions are useful for database administration and analysis. In this section, we’ll explain the basics of MySQL date/time functions and their typical use cases.

Functions to Get the Current Date/Time

Three common functions for retrieving the current date and time in MySQL are NOW(), CURDATE(), and CURTIME().

NOW()

The NOW() function returns the current date and time in the format YYYY-MM-DD HH:MM:SS. It’s useful for logging and timestamping records.

SELECT NOW(); -- Get the current date and time

CURDATE()

The CURDATE() function returns the current date in the format YYYY-MM-DD. It does not include time, so it’s suitable when you only need the date.

SELECT CURDATE(); -- Get the current date

CURTIME()

The CURTIME() function returns the current time in the format HH:MM:SS. Use it when you need only the time without the date.

SELECT CURTIME(); -- Get the current time

Functions to Add/Subtract Dates

To add or subtract intervals from a date, use DATE_ADD() and DATE_SUB(). These functions make it easy to calculate future or past dates.

DATE_ADD()

The DATE_ADD() function adds a specified interval to a date. For example, it’s useful for getting the date 7 days later or 1 month later.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

The DATE_SUB() function subtracts a specified interval from a date. Use it to calculate past dates.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Function to Calculate Date Differences

To calculate the difference between two dates, DATEDIFF() is convenient. For example, you can calculate how many days have passed since a specific date, or the number of days between two dates.

DATEDIFF()

The DATEDIFF() function returns the difference between two dates in days. It’s useful when you want to confirm the number of days from a start date to an end date.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Other Useful Date Functions

MySQL provides additional helpful date functions.

EXTRACT()

The EXTRACT() function extracts a specific part (year, month, day, etc.) from a date. It’s useful when you need only a portion of a date.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

The DATE_FORMAT() function displays a date in a specified format. It’s useful when you want to display dates in a specific format (for example, Japanese-style formatting).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Date Formatting and Conversion Methods

In MySQL, you can change how dates are displayed into a more readable format, or convert date strings into date types. This allows you to flexibly control display formats and consistently manage data even when inputs come in different formats. In this section, we’ll explain the main functions used for date formatting and conversion.

Date Formatting with the DATE_FORMAT() Function

Using DATE_FORMAT(), you can display date values in a specified format. This is especially useful when you need a display format other than the standard one, such as the Japanese “YYYY年MM月DD日” format.

Format Options

With DATE_FORMAT(), you can use format options like the following:

• %Y: 4-digit year
• %y: 2-digit year
• %m: 2-digit month (01–12)
• %d: 2-digit day (01–31)
• %H: 2-digit hour (00–23)
• %i: 2-digit minute (00–59)
• %s: 2-digit second (00–59)

Example Usage

For example, to display the date 2023-01-01 as “2023年01月01日,” you can write the following:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

You can also display a date and time with slashes, like “2023/01/01 12:30:45.”

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Converting Strings to Dates with STR_TO_DATE()

The STR_TO_DATE() function converts a date string into a MySQL date type. For example, it’s useful when you want to treat a string like “2023年01月01日” as a DATE value.

Example Usage

To convert the string “2023-01-01” into a DATE value, write the following:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

To convert a Japanese-style string like “2023年01月01日” into a date, specify the corresponding format:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Examples of Converting Dates from Different Formats

In real-world usage, date input formats can vary, so you may need to convert from different formats. Below are a few examples.

1. Convert “YYYY/MM/DD” to a DATE value

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE

2. Convert “MM-DD-YYYY” to a DATE value

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE

This way, even if data is entered in different formats, you can store and manage it consistently in a unified date format.

Differences Between DATE_FORMAT and STR_TO_DATE, and When to Use Each

• DATE_FORMAT(): Used to change the display format of existing date values. Because the change only affects display, the stored data itself is not modified.
• STR_TO_DATE(): Used to convert a date string into a MySQL DATE or DATETIME value. This changes the stored data type and makes it easier to process dates with other MySQL functions and queries.

Summary

By using DATE_FORMAT() and STR_TO_DATE(), you can flexibly manage how date data is displayed and stored. This makes it possible to handle user or system input more flexibly while maintaining consistent date management. In the next section, we’ll explain date calculations and comparisons, and take a closer look at how to calculate and compare periods using date data.

6. Date Calculations and Comparisons

MySQL provides several convenient functions for date calculations and comparisons. These make it possible to extract data for specific periods or compute date differences for analysis. In this section, we’ll introduce the main functions used for date calculations and comparisons, along with how to use them.

Function to Calculate Date Differences: DATEDIFF()

The DATEDIFF() function returns the difference between two dates in “days.” It’s useful when you want to check elapsed days between dates or determine how many days have passed from a past event until today.

Example Usage

For example, to calculate the number of days from January 1, 2023 to January 10, 2023, write the following:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

In this example, the difference from the start date to the end date is 9 days, so the result is 9.

Calculating Differences by Unit with TIMESTAMPDIFF()

The TIMESTAMPDIFF() function calculates the difference between two dates or datetimes in a specified unit (year, month, day, hour, minute, second). For example, you can compute differences in “months” or “hours” as needed.

Example Usage

1. Calculate a difference in months

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12

2. Calculate a difference in hours

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36

Comparing Dates

Date comparisons are performed using MySQL’s standard comparison operators (<, >, <=, >=, =). This allows you to extract data within a specific period or include past/future dates as conditions.

Example Usage

For example, to extract data “on or after January 1, 2023,” write the following:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Specifying a Range with BETWEEN

The BETWEEN operator lets you specify a date range to retrieve data. It’s useful when you want to extract records that fall within a specific period.

Example Usage

For example, to retrieve data from January 1, 2023 to January 31, 2023, write the following:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Date Calculations with ADDDATE() and SUBDATE()

You can use ADDDATE() and SUBDATE() to add or subtract a number of days from a date. These are convenient for calculating future or past dates.

Example Usage

1. Add 10 days to a date

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11

2. Subtract 10 days from a date

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22

Summary

By using MySQL date calculation and comparison features, you can efficiently extract data for specific periods and analyze data by time ranges. In the next section, we’ll take a closer look at the more advanced topic of “date range searches.”