MySQL is a widely used database management system for building web applications and databases, and handling date data is especially important. For example, managing blog posts, product sales histories, user login logs, and many other scenarios involve date data. Properly managing date data enables efficient data processing and allows you to provide accurate information to users. This guide provides a comprehensive overview of MySQL date handling, covering basic date data types, date functions, calculations using dates, and range queries. It targets beginners to intermediate users and includes concrete query examples that reflect real‑world scenarios, making it useful for practical work. By reading this article, you will be able to:
Understand the characteristics of MySQL date data types and choose the appropriate type based on your data.
Use date functions to easily retrieve and manipulate the current date, past dates, and future dates.
Perform date range searches and comparisons to extract data based on specific time periods.
Now, let’s move on to the next chapter and explore the fundamentals of MySQL dates.
2. Overview of MySQL Date Types
When managing dates in MySQL, it is important to choose the appropriate date type according to the data’s content and purpose. MySQL provides multiple data types for handling dates and times, each with its own characteristics and usage. In this section, we will explain the main date types and their uses in detail.
DATE type
DATE type is a data type that stores only the date (year, month, day) without time. The range is from “1000-01-01” to “9999-12-31”, allowing dates from the year 1000 to 9999 AD. For example, it is suitable for date information that does not consider time, such as birthdays or anniversaries.
CREATE TABLE users (
id INT,
name VARCHAR(50),
birth_date DATE
);
TIME type
TIME type is a data type that stores time (hours, minutes, seconds). The range is from “-838:59:59” to “838:59:59”, and because it can handle negative times, it can also be used to represent time differences. For example, it can be used to record work hours or task durations.
CREATE TABLE work_log (
id INT,
task_name VARCHAR(50),
duration TIME
);
DATETIME type
DATETIME type is a data type that stores both date and time, with a range from “1000-01-01 00:00:00” to “9999-12-31 23:59:59”. It is independent of time zones, allowing you to retrieve the stored timestamp as is. It is suitable for recording the date and time of past events or future appointments.
TIMESTAMP type is a data type that stores date and time and is automatically converted according to the server’s time zone. Its range is from “1970-01-01 00:00:01 UTC” to “2038-01-19 03:14:07 UTC”, making it compatible with Unix epoch time and suitable for recording timestamps and change histories. Additionally, the TIMESTAMP type can default to the current time, making it convenient for automatically recording creation and update dates.
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 type
YEAR type is a data type that stores only the year. Its range is from 1901 to 2155, and it is used when representing a specific year. It is suitable for data that requires year-level management, such as manufacturing year or establishment year.
CREATE TABLE products (
id INT,
name VARCHAR(50),
manufactured_year YEAR
);
Comparison and Uses of Each Date Type
Data Type
Stored Content
Range
Typical Use
DATE
Year, month, day
1000-01-01 – 9999-12-31
Birthdays, anniversaries, etc.
TIME
Hour, minute, second
-838:59:59 – 838:59:59
Work hours, task durations
DATETIME
Year, month, day, hour, minute, second
1000-01-01 00:00:00 – 9999-12-31 23:59:59
Appointments, event timestamps
TIMESTAMP
Year, month, day, hour, minute, second
1970-01-01 00:00:01 UTC – 2038-01-19 03:14:07 UTC
Creation date, update date, automatic logging
YEAR
Year
1901 – 2155
Manufacturing year, establishment year
Conclusion
By selecting MySQL date types according to the characteristics and intended use of the data, you can manage data efficiently and effectively. In the next chapter, we will take a closer look at the differences between the often-confused DATETIME and TIMESTAMP types.
3. Differences between DATETIME and TIMESTAMP types
When handling dates and times in MySQL, the DATETIME type and TIMESTAMP type are commonly used, but they have important differences. Both are data types for storing year, month, day, hour, minute, and second, yet they differ especially in how they handle time zones and the range of values they can store, requiring you to choose based on use case. This section explains in detail the differences and characteristics of DATETIME and TIMESTAMP types.
Features of DATETIME type
Timezone-independent: The DATETIME type is not affected by the server’s timezone setting. Therefore, you can retrieve the stored time exactly as it was saved.
Range: It can handle values from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
Use case: Suitable for data you want to store without depending on a specific timezone, such as past events or future appointments.
Example: Storing an event’s date and time
For example, if you want to keep a globally consistent date, choosing the DATETIME type is appropriate. The example below records an event that takes place at a specific date and time.
In this table, the datetime stored in the event_datetime column is not affected by time zones, and you can retrieve the value exactly as it was saved.
Features of TIMESTAMP type
Timezone-dependent: The TIMESTAMP type is stored and retrieved based on the server’s timezone. Therefore, when moving data between servers in different timezones, conversion according to the timezone is performed.
Range: It can handle values from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (based on the Unix time range).
Automatic update feature: The TIMESTAMP type can automatically record the current time on insert or update by using DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP.
Use case: Suitable when you want to record the current time each time a record’s creation or update timestamp changes.
Example: Storing a post’s creation and update timestamps
This example uses the TIMESTAMP type’s automatic update feature to record a blog post’s creation and update timestamps.
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 setup, when a post is first created, the created_at column records that time, and each time the post is updated, the updated_at column is automatically updated.
Comparison table of DATETIME and TIMESTAMP types
Feature
DATETIME type
TIMESTAMP type
Timezone
Does not depend on server timezone
Depends on server timezone
Range
1000-01-01 00:00:00 ～ 9999-12-31 23:59:59
1970-01-01 00:00:01 UTC ～ 2038-01-19 03:14:07 UTC
Automatic update feature
None
DEFAULT CURRENT_TIMESTAMP etc. supported
Main use case
When you want to record a fixed time
When automatic recording of creation or update timestamps is needed
Choosing guidelines
If you don’t want timezone influence, choose DATETIME type: For example, when storing an event date and time for a specific country or region’s timezone, the DATETIME type is appropriate.
If you want automatic updates and timezone handling, choose TIMESTAMP type: For example, when you want the database to automatically record when data was updated, the TIMESTAMP type is convenient.
Summary
The DATETIME type and TIMESTAMP type each have distinct characteristics, and using the appropriate one based on the use case allows efficient data management. In the next chapter, we’ll take a detailed look at the essential date functions for MySQL date manipulation.
4. Basics of MySQL Date Functions
MySQL provides a variety of functions for working with dates and times. From functions that retrieve the current date or time to those that add or subtract dates, there are many functions useful for database management and analysis. This section explains the basic usage of MySQL date functions and their respective purposes.
Functions to Retrieve the Current Date and Time
The primary functions used to get the current date and time in MySQL are NOW(), CURDATE(), and CURTIME().
NOW()
NOW() function returns the current date and time in the format “YYYY-MM-DD HH:MM:SS”. It’s useful for recording timestamps and creating logs.
SELECT NOW(); -- Get the current date and time
CURDATE()
CURDATE() function returns the current date in the format “YYYY-MM-DD”. Since it does not include time, it’s suitable when you only need to manage dates.
SELECT CURDATE(); -- Get the current date
CURTIME()
CURTIME() function returns the current time in the format “HH:MM:SS”. Use it when you need only the time without a date.
SELECT CURTIME(); -- Get the current time
Functions for Adding and Subtracting Dates
When you want to add or subtract a specific interval to a date, use the DATE_ADD() and DATE_SUB() functions. They make it easy to calculate future or past dates.
DATE_ADD()
DATE_ADD() function adds a specified interval to a date. It’s handy for getting dates like 7 days later or one month later.
When you need to calculate the difference between two dates, the DATEDIFF() function is useful. For example, you can compute the number of days from a specific date to today, or find the days between two dates.
DATEDIFF()
DATEDIFF() function returns the difference between two dates in days. It’s handy for checking the number of days from a start date to an end date.
MySQL also provides several other handy date-related functions.
EXTRACT()
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 value.
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()
DATE_FORMAT() function displays a date in a specified format. Use it when you want to show dates in formats such as the Japanese style (YYYY年MM月DD日).
MySQL date functions enable a wide range of operations, including retrieving the current date and time, adding or subtracting dates, calculating differences, and extracting parts. Date manipulation is essential for data analysis and management, and using these functions allows you to manage data efficiently. In the next chapter, we’ll explore date formatting and conversion methods in more detail.
5. Date Formatting and Conversion Methods
In MySQL, you can change data to a more readable format when displaying it, or convert string-formatted date data to a date type. This allows you to flexibly set the display format and consistently manage data in different formats. This section explains the main functions used for date formatting and conversion.
Date Formatting with the DATE_FORMAT() Function
DATE_FORMAT() function can be used to display date data in a specified format. It is especially useful when you need a display other than the standard format, such as the Japanese “YYYY year MM month DD day” style.
Format Options
In DATE_FORMAT(), you can use the following format options.
%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 would do the following.
Converting Strings to Dates with the STR_TO_DATE() Function
STR_TO_DATE() function converts string-formatted date data to a date type. For example, it is useful when you want to treat a string like “2023-01-01” as a DATE type.
Example Usage
To convert the string “2023-01-01” to a DATE type, write the following.
SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- returns 2023-01-01 as DATE type
Also, when converting a Japanese-formatted string “2023-01-01” to a date, specify the corresponding format.
SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- returns 2023-01-01 as DATE type
Examples of Date Conversion with Different Formats
In practice, input date formats may vary, requiring conversion from different formats. Below are some examples.
Conversion from “YYYY/MM/DD” format to DATE type
SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- returns 2023-01-01 as DATE type
Conversion from “MM-DD-YYYY” format to DATE type
SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- returns 2023-01-01 as DATE type
This allows data entered in various formats to be stored and managed in a consistent date format.
Differences and Use Cases for DATE_FORMAT and STR_TO_DATE
DATE_FORMAT(): Used to change the display format of existing date data. Changing the format only affects how it is shown; the stored data itself remains unchanged.
STR_TO_DATE(): Used to convert string-formatted date data to MySQL DATE or DATETIME types. The stored data type changes, making date handling in other MySQL functions and queries easier.
Summary
By leveraging DATE_FORMAT() and STR_TO_DATE(), you can flexibly manage the display and storage formats of date data. This enables you to handle input from users and systems flexibly and maintain consistent date management. In the next chapter, we will discuss date calculations and comparisons, and take a detailed look at how to compute and compare periods using date data.
6. Date Calculations and Comparisons
MySQL provides several convenient functions for performing date calculations and comparisons. This allows you to extract data for specific periods and calculate date differences for analysis. In this section, we introduce the main functions used for date calculations and comparisons, along with how to use them.
Function to calculate date difference: DATEDIFF()
DATEDIFF() function returns the difference between two dates in days. It is useful when you want to check the number of days elapsed between specific dates or the days passed from a past event to the present.
Example
For example, to calculate the number of days from January 1, 2023 to January 10, 2023, you would write:
In this example, the difference between the start date and end date is 9 days, so the result returned is 9.
Period unit difference calculation with TIMESTAMPDIFF()
TIMESTAMPDIFF() function calculates the difference between two dates or datetimes in the specified unit (year, month, day, hour, minute, second). For example, you can obtain the difference in months for a period or in hours.
Date comparison is performed using MySQL’s standard comparison operators (<, >, <=, >=, =). This allows you to extract data within a specific period or include past or future dates as conditions.
Example
For example, to retrieve data on or after January 1, 2023, you would write:
SELECT * events WHERE event_date >= '2023-01-01';
Range specification using BETWEEN
Using the BETWEEN operator, you can specify a date range to retrieve data. This is handy when you want to extract data that falls within a particular period.
Example
For example, to get data from January 1, 2023 to January 31, 2023, you would write:
SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';
Date calculations with ADDDATE() and SUBDATE()
By using the ADDDATE() and SUBDATE() functions, you can add or subtract days from a specific date. This is useful for calculating future or past dates.
By leveraging MySQL’s date calculation and comparison features, you can efficiently extract data related to specific periods and perform period-based data analysis. In the next chapter, we will dive deeper into advanced “date range searches.”
7. Date Range Search
MySQL often requires searching for data that falls within a specific period. Performing date range searches efficiently enables extraction of data based on detailed conditions, such as “events held in a particular month” or “data from the past week.” This section explains techniques and concrete query examples useful for date range searches in MySQL.
Basic Range Search: BETWEEN Clause
The BETWEEN clause is handy for retrieving date data within a specified range. Using the BETWEEN clause allows you to easily extract data from the start date to the end date you specify.
Example
For instance, to retrieve data from January 1 2023 through January 31 2023, write the following:
SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';
This query returns all rows where event_date falls between January 1 2023 and January 31 2023.
Range Search Using Comparison Operators
Instead of the BETWEEN clause, you can perform range searches with comparison operators such as >= and <=. This approach lets you set start and end dates more flexibly and specify finer‑grained conditions.
Example
To retrieve only data on or after January 1 2023, write:
SELECT * FROM events WHERE event_date >= '2023-01-01';
To retrieve data that falls between January 1 2023 and January 31 2023, use:
SELECT * FROM events WHERE event_date >= '2023-01-01' AND event_date <= '2023-01-31';
Specifying Dynamic Date Ranges
Dynamic date ranges are useful when you need data from a period relative to a specific date. For example, retrieving “the past 30 days of data” or “the most recent week of data” can be done dynamically.
Example: Retrieve Data from the Past 30 Days
Use the CURDATE() function to get data from the past 30 days based on the current date.
SELECT * FROM events WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
This query returns rows with dates on or after the date 30 days before CURDATE().
Example: Retrieve Data for a Specific Future Period
You can also perform range searches based on future dates. For example, to get events scheduled from today up to one month ahead, write:
SELECT * FROM events WHERE event_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
Retrieving Recurring Data
When you want data based on specific weekdays or months, combine functions like WEEKDAY() and MONTH() to set conditions for extracting data by day or month.
Example: Retrieve Data for a Specific Day Each Month
To get only the data for the 1st day of each month, use the DAY() function as follows:
SELECT * FROM events WHERE DAY(event_date) = 1;
Example: Retrieve Data for a Specific Weekday
The WEEKDAY() function can also be used to fetch rows that match a particular weekday. For example, to get data for every Monday, write:
SELECT * FROM events WHERE WEEKDAY(event_date) = 0; -- Monday is 0, Tuesday is 1, and so on
Improving Range Search Performance
When the data volume is large, range searches can slow down query execution. To boost performance, it is recommended to add an index on the date column.
Example: Creating an Index
Adding an index to the event_date column improves the performance of range searches.
CREATE INDEX idx_event_date ON events(event_date);
With an index, the database can process date range searches efficiently, resulting in faster query execution.
Summary
MySQL date range searches can be performed efficiently using the BETWEEN clause or comparison operators. You can also set dynamic date ranges and specify weekdays, providing flexible search capabilities for various conditions. The next chapter covers automatic date updates and setting default values.
8. Automatic Date Updates and Default Value Settings
MySQL provides convenient features for automatically recording the creation and update timestamps of records. In particular, you can set the current time as the default value for TIMESTAMP or DATETIME date fields, or configure them to automatically update the time whenever a record is modified. This allows creation and last‑update times to be recorded automatically, streamlining data management. This section explains how to configure automatic date updates and default values in MySQL.
Automatically Setting the Current Time
In MySQL, you can set the default value of TIMESTAMP or DATETIME columns to the current time. When a new record is inserted and no value is provided for the column, the current time is automatically inserted.
Example: Automatic Creation Timestamp
For instance, to automatically record the time a record is created in a created_at column, you would configure it as follows.
CREATE TABLE blog_posts (
id INT PRIMARY KEY,
title VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
With this setting, each time a new record is added, the created_at column is automatically populated with the current time.
Automatic Update Setting: ON UPDATE CURRENT_TIMESTAMP
MySQL also allows you to configure a column to automatically update its timestamp whenever its value changes. This enables automatic recording of the data’s last‑update time.
Example: Automatic Update Timestamp
For example, to automatically record the current time in an updated_at column each time a record is updated, you would set it up like this.
CREATE TABLE blog_posts (
id INT PRIMARY KEY,
title VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
With this configuration, both created_at and updated_at receive the current time when a new record is created, and thereafter the updated_at column is automatically refreshed to the current time each time the record is updated.
Automatic Update Settings for DATETIME Columns
Starting with MySQL 5.6, the DATETIME type also supports DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. However, automatic update settings can be applied to only one DATETIME column, so if you need multiple auto‑updating columns, using TIMESTAMP is preferable.
Example: Automatic Setting for DATETIME
CREATE TABLE blog_entries (
id INT PRIMARY KEY,
title VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
In this example, created_at is automatically set to the record’s creation time, but it does not have the automatic update capability that TIMESTAMP provides.
Use Cases for Automatic Updates and Default Values
Automatic date settings are especially handy for managing creation and update timestamps. They are commonly used to track when blog posts were published, when users last logged in, when orders were updated, and similar scenarios.
Example: User’s Last Login Timestamp
For instance, when designing a table to track users’ last login times, you would configure the last_login column to update automatically.
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
With this setting, each time a user logs in, last_loginThings to Note
Multiple auto‑update fields: In MySQL, having more than one auto‑updating field in a table is only possible with TIMESTAMP columns. The DATETIME type cannot have automatic update settings applied to multiple columns.
Version dependency: Automatic updates for the DATETIME type are supported only in MySQL 5.6 and later, so the feature depends on the server version. Be sure to check your MySQL version before using it.
Summary
By leveraging MySQL’s automatic date updates and default value settings, you can efficiently manage creation and update timestamps. Understanding the characteristics of TIMESTAMP and DATETIME types and choosing the appropriate type and settings will simplify data management. In the next chapter, we’ll look at concrete examples of date manipulation based on real‑world scenarios.
9. Practical Example: Using MySQL Dates
MySQL date functions are used in a variety of scenarios in real-world work. For example, they are often used to aggregate data for a specified period or to analyze historical data using dates. In this section, we present several concrete examples of date operations imagined for actual business situations. This will help you understand how MySQL date functions can be leveraged and make it easier to apply them in practice.
Retrieve Data from the Past 30 Days
Extracting data from the past 30 days is a common operation for analyzing access logs, sales data, and similar. Here we present a query that retrieves data that occurred in the past 30 days based on the current date.
Example: Extracting Access Logs
SELECT * FROM access_logs WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
This query extracts rows where log_date falls within 30 days of the current date (CURDATE()). For example, it is useful for analyzing recent access trends.
Aggregate Sales Data for a Specific Month
Aggregating sales data for a particular month is also important for sales management and marketing activities. Here we present a query that calculates the total sales for all transactions that occurred in January 2023.
Example: January 2023 Sales Summary
SELECT SUM(amount) AS total_sales
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';
This query retrieves the total sales amount for rows where sale_date falls within January 2023. Using the SUM() function calculates the sum of sales for the specified period.
Retrieve Data for a Specific Day of the Week
If you want to analyze data by day of the week, you can use the WEEKDAY() function to extract rows that correspond to a specific weekday. For example, it is useful for retrieving events that occurred every Monday.
Example: Retrieve Weekly Monday Events
SELECT * FROM events WHERE WEEKDAY(event_date) = 0; -- Monday is 0
This query retrieves events where event_date falls on a Monday. It is handy for analyzing event trends by weekday.
Trend Analysis Using Dates
When analyzing trends based on dates, it is common to aggregate data by day, week, or month to understand patterns. Here we present a query that aggregates daily access counts for a one‑month period.
Example: Daily Access Count Aggregation
SELECT DATE(log_date) AS date, COUNT(*) AS access_count
FROM access_logs
WHERE log_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()
GROUP BY DATE(log_date);
This query aggregates the access logs from the past month by day and retrieves the access count for each date. It is a suitable method for understanding daily access trends.
Display Monthly Sales Trends
If you want to track sales trends on a monthly basis, you can combine the YEAR() and MONTH() functions to aggregate sales data by month.
Example: Monthly Sales Aggregation
SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY year, month;
This query retrieves the total sales for each month and orders the results by year and month. Understanding monthly sales trends helps analyze seasonal sales patterns and trends.
Retrieve Data for a Specific Time Range
To investigate cases where access spikes during a particular time of day, you can retrieve data using a time range condition. For example, to examine the number of accesses that occurred in the morning, you can use the HOUR() function.
Example: Retrieve Morning Access Data
SELECT * FROM access_logs WHERE HOUR(log_time) BETWEEN 9 AND 12;
This query retrieves access logs where log_time falls between 9 AM and 12 PM. It is useful for analyzing access trends by time of day.
Conclusion
By leveraging date data, you can grasp data trends and detailed information for specific periods. Using the examples above, apply MySQL date functions to extract and analyze data according to your goals. In the next chapter, we’ll move to an FAQ section that compiles common questions and their answers.
10. FAQ
This section compiles the most frequently asked questions and answers about MySQL date handling. It resolves doubts about how to execute specific queries and manage data, helping you use MySQL date functions more effectively.
Frequently Asked Questions and Answers
Q1. How do I get the current date in MySQL?
A1. To retrieve the current date and time, use the NOW() function. If you only need the current date, the CURDATE() function is handy.
SELECT NOW(); -- current date and time
SELECT CURDATE(); -- current date only
Q2. How do I format a date in a specific format in MySQL?
A2. To display a date in a specific format, use the DATE_FORMAT() function. For example, to show it as “YYYY-MM-DD”, you would write:
Q3. What is the difference between TIMESTAMP and DATETIME?
A3. The TIMESTAMP type is dependent on the server’s time, ensuring consistency of date‑time values across servers in different zones. It also supports automatic updating. In contrast, the DATETIME type is time‑zone independent and stores the specified date‑time as‑is.
Q4. How do I add or subtract dates in MySQL?
A4. Use DATE_ADD() to add dates and DATE_SUB() to subtract them. For example, to get the date one week later:
Q5. How do I get the difference between two dates in days in MySQL?
A5. You can use the DATEDIFF() function to obtain the number of days between two dates. For example, to get the days from January 1, 2023 to January 10, 2023:
SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- 9
Q6. How do I set automatic updates for a date column?
A6. By specifying the ON UPDATE CURRENT_TIMESTAMP option on TIMESTAMP or DATETIME columns, you can enable automatic updates. This is especially useful for recording the last modified time.
CREATE TABLE posts (
id INT PRIMARY KEY,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Q7. How do I retrieve data from the past 30 days?
A7. You can get the current date with CURDATE() and use DATE_SUB() to retrieve data from the past 30 days.
SELECT * FROM events WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
Q8. How do I retrieve data for a specific weekday in MySQL?
A8. The WEEKDAY() function lets you filter data by weekday. For example, to get events that occurred on Monday:
SELECT * FROM events WHERE WEEKDAY(event_date) = 0; -- Monday is
Summary
These are the FAQs about MySQL date handling. The questions and answers address common doubts about manipulating date data and help you manage your data efficiently. This covers the entire article, and the next section will recap the key points of date handling.
11. Summary
In this article, we explained in detail various methods for date manipulation in MySQL, from basics to advanced techniques. Date data is a crucial element for effectively managing databases and supporting business decision‑making. Let’s review the key points.
Key Takeaways
Choosing a date type: Understanding the differences among MySQL date types such as DATE, TIME, DATETIME, TIMESTAMP, and YEAR and selecting the appropriate type for your use case is essential.
Differences between DATETIME and TIMESTAMP: The DATETIME type is timezone‑independent and suited for storing fixed points in time, while the TIMESTAMP type depends on the timezone and is ideal for automatically recording creation and update timestamps.
Using date functions: Functions like NOWcode>, CURDATE(), DATE_ADD(), and DATE_SUB() make it easy to retrieve and calculate dates.
Date formatting and conversion: You can change display formats with DATE_FORMAT() and convert strings to dates with STR_TO_DATE(), enabling flexible data management.
Date range queries: By leveraging the BETWEEN clause and comparison operators, you can efficiently extract data for specific periods.
Automatic updates and default values: Setting automatic updates or default values on TIMESTAMP or DATETIME columns simplifies the management of creation and modification timestamps.
Practical use cases: We also presented example queries that use date manipulation to extract data for particular periods or aggregate monthly sales trends—useful for real‑world applications.
How to Leverage MySQL Date Functions
MySQL’s date features can be applied across a wide range of scenarios, from everyday data management to detailed analysis. By correctly understanding and using date operations in your database, you can make data management more efficient and greatly benefit business operations. When new database needs, refer to the content of this article and apply MySQL’s date capabilities appropriately.
