MySQL Date and Time Functions

Choose the Correct Temporal Type

Do not store dates as DD-MM-YYYY text. Native types validate, sort and calculate correctly.

Current Date and Time

SELECT CURRENT_DATE AS today,
       CURRENT_TIME AS current_time,
       NOW() AS current_date_time,
       UTC_TIMESTAMP() AS utc_date_time;

Current functions depend on server/session context. For reproducible examples below, the literal date 2026-08-14 is used.

Extract Date Parts

SELECT DATE('2026-08-14 10:30:00') AS date_only,
       YEAR('2026-08-14') AS year_no,
       MONTH('2026-08-14') AS month_no,
       DAY('2026-08-14') AS day_no,
       MONTHNAME('2026-08-14') AS month_name;

Functions on a WHERE column can prevent efficient range index use. Extract for display, but filter indexed timestamps with ranges when possible.

Date Arithmetic

SELECT DATEDIFF('2026-08-21', '2026-08-14') AS days_gap,
       DATE_ADD('2026-08-14', INTERVAL 10 DAY) AS after_10_days,
       DATE_SUB('2026-08-14', INTERVAL 1 MONTH) AS previous_month,
       LAST_DAY('2026-02-10') AS month_end;

Month arithmetic can adjust invalid target days; test end-of-month rules for fees, subscriptions and attendance.

Format for Display, Not Storage

SELECT DATE_FORMAT('2026-08-14', '%d-%m-%Y') AS display_date,
       STR_TO_DATE('14-08-2026', '%d-%m-%Y') AS parsed_date;

Parsing external text should be validated. Store the parsed native value, not the display string.

Safe Ranges and Time Zones

SELECT student_id, full_name, created_at
FROM students
WHERE created_at >= '2026-08-01'
  AND created_at <  '2026-09-01'
ORDER BY created_at;

This includes all of August regardless of time-of-day precision. Define the business time zone, set the session correctly and convert only at system boundaries. Mixing local DATETIME and UTC assumptions causes off-by-one-day reports.

Practice

1. Find the last day of August 2026.
2. Add 45 days to 14 August 2026.
3. Format a stored DATE for Indian display.
4. Write a half-open range for September 2026.
5. Explain TIMESTAMP vs DATETIME for an online login event.

Quick Summary

• Use native temporal types.
• Current functions use session context.
• Extract, calculate and format with dedicated functions.
• Filter time periods with half-open ranges.
• Define a clear time-zone policy.

Official References

• Date and Time Functions — MySQL 8.4
• Date and Time Data Types
• DATE, DATETIME and TIMESTAMP Types

References reviewed 14 August 2026.