Home/Learn/MySQL/Date & Time Functions

Date & Time Functions

Beginner

Advanced Queries

NOW(), CURDATE(), DATE_ADD(), DATEDIFF(), DATE_FORMAT(), UNIX_TIMESTAMP(), and STR_TO_DATE() are essential for date arithmetic, formatting, and timezone-aware applications.

Overview

MySQL has a rich set of date and time functions for generating, formatting, arithmetic, and converting between representations. NOW() returns the current datetime at statement start; SYSDATE() returns the time at function execution (different inside stored procedures). DATE_ADD/DATE_SUB operate on intervals (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR). DATEDIFF returns whole days; TIMESTAMPDIFF allows any unit. DATE_FORMAT formats a date with strftime-style specifiers. Server timezone affects NOW() and TIMESTAMP columns; store in UTC and convert in the application layer for portability.

Common Date Functions

The most-used functions cover current time, arithmetic, and difference calculations. Avoid wrapping indexed columns in functions (e.g. DATE(created_at) = CURDATE()) — use range comparisons instead to keep indexes effective.

SQL — date arithmetic and index-friendly comparisons

-- Current date/time
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)  AS next_week,
       DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month;

-- Difference
SELECT DATEDIFF('2025-12-31', '2025-01-01')        AS days_diff,   -- 364
       TIMESTAMPDIFF(MONTH, '2024-01-01', NOW())    AS months_since;

-- Index-friendly range query (avoids function on column)
SELECT * FROM orders
WHERE created_at >= CURDATE()
  AND created_at <  CURDATE() + INTERVAL 1 DAY;

Formatting & Parsing

DATE_FORMAT converts a date to a string using format codes. STR_TO_DATE is the inverse. UNIX_TIMESTAMP / FROM_UNIXTIME bridge between epoch integers and MySQL datetimes.

SQL — DATE_FORMAT, STR_TO_DATE, epoch conversion

-- Format for display
SELECT DATE_FORMAT(NOW(), '%d %b %Y %H:%i') AS human_date;
-- Output: 22 Mar 2026 14:30

-- Parse a user-supplied string
SELECT STR_TO_DATE('22-03-2026', '%d-%m-%Y') AS parsed_date;

-- Epoch interop
SELECT UNIX_TIMESTAMP(NOW())            AS epoch_now,
       FROM_UNIXTIME(1742652600)        AS from_epoch;

-- Extract parts
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()),
       HOUR(NOW()), MINUTE(NOW()), DAYOFWEEK(NOW());

Timezone Handling

TIMESTAMP columns are stored in UTC and converted to the session timezone on read; DATETIME columns store exactly what you insert with no conversion. Set the session timezone per connection for multi-region apps.

SQL — TIMESTAMP vs DATETIME timezone behaviour

-- Check server and session timezone
SELECT @@global.time_zone, @@session.time_zone;

-- Set session timezone (e.g. per API request in connection pool init)
SET time_zone = '+05:30';

-- TIMESTAMP auto-converts; DATETIME does not
CREATE TABLE events (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    ts_col       TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,  -- UTC stored, local on read
    dt_col       DATETIME    DEFAULT CURRENT_TIMESTAMP   -- stored as-is
);

-- Best practice: store in UTC, convert in application layer
SELECT CONVERT_TZ(ts_col, @@session.time_zone, '+00:00') AS utc_time FROM events;

Key Points to Remember

1NOW() is evaluated once per statement; SYSDATE() is evaluated at call time — matters inside loops
2Never apply functions to indexed date columns in WHERE — use range comparisons instead
3DATEDIFF() returns integer days; TIMESTAMPDIFF(UNIT, start, end) supports any unit
4DATE_FORMAT uses % specifiers: %Y (4-digit year), %m (month), %d (day), %H:%i:%s (time)
5TIMESTAMP is auto-converted from/to session timezone; DATETIME stores verbatim
6Store all timestamps in UTC in the DB; apply timezone conversion in the application layer

Interview Questions

What is the difference between NOW() and SYSDATE() in MySQL?

MediumInfosys

Why should you avoid DATE(created_at) = CURDATE() in a WHERE clause?

MediumTCS

What is the difference between TIMESTAMP and DATETIME column types?

EasyWipro

How do you calculate the number of months between two dates?

EasyAccenture

How would you design a multi-timezone application using MySQL?

HardBooking.com

Ask Aria about Date & Time Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

String Functions

Indexes Overview