Leave a Comment / SQL / By codeswithpankaj@gmail.com

MySQL Date and Time Functions

Managing temporal data is a crucial part of database operations. MySQL provides a comprehensive set of functions to retrieve, extract, manipulate, and format date and time values. In this guide, we’ll break down these functions step-by-step using real-world examples.

1. Setting Up the Sample Table

To demonstrate each function, let’s use a sample table named events, which stores event names and their scheduled DATETIME.

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATETIME
);

INSERT INTO events (event_name, event_date) VALUES
('Conference', '2025-05-05 09:00:00'),
('Workshop', '2025-05-10 14:30:00'),
('Meeting', '2025-06-01 10:00:00');

2. MySQL Date and Time Functions: Categorized with Examples

2.1. Functions to Retrieve Current Date and Time

Function	Purpose
`NOW()`	Current date and time (DATETIME)
`CURDATE()` / `CURRENT_DATE()`	Current date (DATE)
`CURTIME()` / `CURRENT_TIME()`	Current time (TIME)
`CURRENT_TIMESTAMP()`	Alias for `NOW()`

SELECT NOW();             -- e.g., 2025-05-05 14:30:00
SELECT CURDATE();         -- e.g., 2025-05-05
SELECT CURTIME();         -- e.g., 14:30:00
SELECT CURRENT_TIMESTAMP(); -- e.g., 2025-05-05 14:30:00

2.2. Extracting Components from Date/Time

Function	Extracts
`YEAR(date)`	Year
`MONTH(date)`	Month
`DAY(date)` / `DAYOFMONTH(date)`	Day of the month
`HOUR(time)`	Hour
`MINUTE(time)`	Minute
`SECOND(time)`	Second
`EXTRACT(unit FROM date)`	General-purpose extraction

SELECT event_name, YEAR(event_date) AS year FROM events;
SELECT event_name, MONTH(event_date) AS month FROM events;
SELECT event_name, DAY(event_date) AS day FROM events;
SELECT event_name, HOUR(event_date) AS hour FROM events;
SELECT event_name, MINUTE(event_date) AS minute FROM events;
SELECT event_name, SECOND(event_date) AS second FROM events;
SELECT event_name, EXTRACT(WEEK FROM event_date) AS week FROM events;

2.3. Manipulating Dates and Times

Function	Description
`DATE_ADD(date, INTERVAL value unit)`	Adds interval
`DATE_SUB(date, INTERVAL value unit)`	Subtracts interval
`DATEDIFF(date1, date2)`	Days between dates
`TIMEDIFF(time1, time2)`	Time difference

SELECT event_name, DATE_ADD(event_date, INTERVAL 3 DAY) AS plus_3_days FROM events;
SELECT event_name, DATE_SUB(event_date, INTERVAL 2 HOUR) AS minus_2_hours FROM events;
SELECT event_name, DATEDIFF('2025-06-01', event_date) AS days_left FROM events;
SELECT event_name, TIMEDIFF(event_date, '2025-05-05 08:00:00') AS time_diff FROM events;

2.4. Formatting and Parsing Dates

Function	Description
`DATE_FORMAT(date, format)`	Format a date as a string
`STR_TO_DATE(str, format)`	Parse a string into a date

SELECT event_name, DATE_FORMAT(event_date, '%W, %M %d, %Y %H:%i') AS formatted FROM events;
SELECT STR_TO_DATE('May 05, 2025 09:00', '%M %d, %Y %H:%i') AS parsed_date;

2.5. Miscellaneous Date Functions

Function	Description
`DAYOFWEEK(date)`	Day index (1=Sunday, 7=Saturday)
`DAYOFYEAR(date)`	Day of the year
`WEEK(date, mode)`	Week number
`UNIX_TIMESTAMP([date])`	Seconds since Unix epoch
`FROM_UNIXTIME(timestamp, format)`	Converts Unix timestamp to date

SELECT event_name, DAYOFWEEK(event_date) AS weekday FROM events;
SELECT event_name, DAYOFYEAR(event_date) AS doy FROM events;
SELECT event_name, WEEK(event_date, 1) AS week_num FROM events;
SELECT event_name, UNIX_TIMESTAMP(event_date) AS unix_ts FROM events;
SELECT FROM_UNIXTIME(1746402000, '%Y-%m-%d %H:%i') AS from_unix;

3. Real-World Query Example: Upcoming Events

SELECT 
    event_name,
    DATE_FORMAT(event_date, '%W, %b %d, %Y %H:%i') AS formatted_date,
    DATEDIFF(event_date, CURDATE()) AS days_until,
    DAYOFWEEK(event_date) AS weekday
FROM events
WHERE event_date >= CURDATE();

Sample Output:

event_name	formatted_date	days_until	weekday
Conference	Monday, May 05, 2025 09:00	0	2
Workshop	Saturday, May 10, 2025…	5	7
Meeting	Sunday, Jun 01, 2025…	27	1