SoFunction
Updated on 2025-03-04

MySQL date ATE, TIME, DATETIME, TIMESTAMP and YEAR usage statements

In MySQL databases, processing of date and time data types is a very important part. Correctly using date-related statements can help us effectively store, query, and operate date data. This article will introduce some common date usage statements in MySQL and explore their usage scenarios.

Date data types in MySQL

MySQL provides a variety of date and time data types, including DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

  • DATE: used to store date values, in the format YYYY-MM-DD.
  • TIME: used to store time values, format HH:MM:SS.
  • DATETIME: used to store date and time values, in the format YYYY-MM-DD HH:MM:SS.
  • TIMESTAMP: Similar to DATETIME, but its value will be automatically updated every time it is updated.
  • YEAR: used to store year values, in the format YYYY.

Example explanation of common date usage statements

Get the current date and time

  1. CURDATE(): Returns the current date, the format is YYYY-MM-DD.
  • Example:
SELECT CURDATE();
  • This will return the current date, for example 2024-11-29.
  1. CURTIME(): Returns the current time, format is HH:MM:SS.
  • Example:
SELECT CURTIME();
  • This will return the current time, for example 12:34:56.
  1. NOW(): Returns the current date and time, the format is YYYY-MM-DD HH:MM:SS.
  • Example:
SELECT NOW();
  • This will return the current date and time, for example 2024-11-29 12:34:56.

Date extraction and formatting

  1. YEAR(), MONTH(), DAY(): Used to extract the year, month and date parts in the date respectively.
  • Example:
SELECT YEAR('2024-11-29'), MONTH('2024-11-29'), DAY('2024-11-29');
  • This will return 2024, 11 and 29.
  1. DATE_FORMAT(): Used to format dates into specific string formats.
  • Example:
SELECT DATE_FORMAT('2024-11-29', '%Y-%m-%d %H:%i:%s');
  • This will return 2024-11-29 00:00:00. where %Y represents four-digit year, %m represents two-digit month, %d represents two-digit date, %H represents two-digit hours, %i represents two-digit minutes, and %s represents two-digit seconds.

Calculation of dates

  1. DATE_ADD() and DATE_SUB(): Used to add or subtract a time interval on a date.
  • Example:
SELECT DATE_ADD('2024-11-29', INTERVAL 7 DAY);
SELECT DATE_SUB('2024-11-29', INTERVAL 7 DAY);
  • The first query will return 2024-12-06, indicating that 7 days are added to 2024-11-29. The second query will return 2024-11-22, indicating that 7 days are subtracted from 2024-11-29.
  1. DATEDIFF(): used to calculate the difference in the number of days between two dates.
  • Example:
SELECT DATEDIFF('2024-12-06', '2024-11-29');
  • This will return 7, indicating a 7-day difference between the two dates.

Use scenario description

Record the time of the event

In many applications, it is necessary to record the time when the event occurs, such as user registration time, order creation time, etc. These time information can be stored using the DATETIME or TIMESTAMP data types.

  • Example:
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATETIME
);

INSERT INTO orders (customer_id, order_date) VALUES (1, NOW());
  • In this example, a table named orders is created, where the order_date column is used to store the time the order was created. Each time a new order is inserted, use the NOW() function to get the current time and insert it into the order_date column.

Query data within a specific time period

In data analysis and report generation, it is often necessary to query data within a specific time period. This function can be implemented using date functions and conditional statements.

  • Example:
SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';
  • This query will return all sales records that occurred in 2024. The date range can be adjusted according to actual needs.

Clean out expired data regularly

Some applications require regular cleaning of expired data to keep the database neat and efficient. This function can be implemented using date functions and DELETE statements.

  • Example:
DELETE FROM sessions WHERE session_expire_date < NOW();
  • In this example, all expired session records are deleted. The session_expire_date column stores the expiration time of the session. When the current time is greater than session_expire_date, it means that the session has expired and can be deleted.

Generate reports and statistics

The date function can be used to generate various reports and statistics, such as counting sales by month, quarter or year, user activity, etc.

  • Example:
SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, SUM(order_amount) AS total_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);
  • This query will group the sum of order amounts by year and month to generate a sales report. Statistical dimensions and indicators can be adjusted according to actual needs.

in conclusion

Date usage statements in MySQL provide powerful functions to meet the needs of various application scenarios. By using these statements correctly, we can effectively store, query and operate date data, providing strong support for application development and data analysis.

This is the article about MySQL date ATE, TIME, DATETIME, TIMESTAMP and YEAR usage statements. For more related contents of MySQL date usage statements, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!