Date and Time Functions

What Is It?

What Are Date and Time Functions?

Dates in SQL are not just strings. MySQL stores them as structured values that understand the calendar — leap years, daylight saving time, the last day of February, the number of days between two dates. Date and time functions are the set of built-in functions MySQL ships to read the current moment, extract parts from a date, shift dates forward or backward, and format them for display.

Once orders, signups, payments, and attendance records land in a database, almost every business question is a date question. "How many orders did we get last week?" "Which customers haven't logged in for 30 days?" "Give me revenue grouped by month for the last year." Each of these needs a date function.

-- Today's date and the exact moment right now
SELECT CURDATE(), NOW();
-- 2026-04-16 | 2026-04-16 14:23:07

-- Orders placed in the last 7 days
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY;

Every date function in MySQL falls into one of four buckets: getting the current time (NOW, CURDATE, CURTIME), extracting parts (YEAR, MONTH, DAY, HOUR), arithmetic (DATE_ADD, DATE_SUB, DATEDIFF, TIMESTAMPDIFF), and formatting / parsing (DATE_FORMAT, STR_TO_DATE).

The Three Storage Types

Before writing a single function, know where your data lives. MySQL has three main date-related column types, and picking the wrong one will hurt you later:

-- DATE: just a calendar date, 3 bytes, no time, no timezone
order_date DATE               -- '2026-04-16'

-- DATETIME: date + time, 8 bytes, NO timezone awareness
logged_in_at DATETIME         -- '2026-04-16 14:23:07'

-- TIMESTAMP: date + time, 4 bytes, stored as UTC internally,
-- auto-converts to the session's time zone on read
created_at   TIMESTAMP        -- shown as IST, stored as UTC

Why Does It Matter?

Why Date Functions Matter

1. Every Business Report Is a Date Query

Open any analytics dashboard — Flipkart seller panel, Zomato restaurant dashboard, a school's fee dashboard — and more than half the numbers are grouped by day, week, or month. Sales today vs yesterday. Week-over-week growth. Active users in the last 30 days. Without fluent date functions, you cannot even start writing these queries.

2. Filtering Is the #1 Use Case

"Give me everything from the last 7 days" is the most common WHERE clause in the database. You cannot write it without CURDATE() and INTERVAL. A data analyst who cannot filter by a rolling window is stuck.

3. Dates as Strings Are a Bug Waiting to Happen

Beginners often store dates as VARCHAR like '16-04-2026'. Sorting breaks, date arithmetic is impossible, and '31-01-2026' > '01-02-2026' evaluates to true (wrong). Using real DATE columns plus date functions prevents this entire class of bug.

4. Time Zones Bite in Production

When Modern Age Coders launches a course at 7 PM IST, the database server might be in UTC, the user's laptop in EST. If you don't understand TIMESTAMP versus DATETIME, users will see the wrong start time. This chapter covers CONVERT_TZ() so you don't ship that bug.

5. Interview Favorites Are Date-Heavy

"Find customers whose orders are at least 30 days apart", "Get month-over-month growth", "List employees with more than 2 years tenure" — these questions depend entirely on DATEDIFF, TIMESTAMPDIFF, and DATE_ADD. They appear in almost every data analyst interview.

Detailed Explanation

Detailed Explanation

1. Getting the Current Moment

MySQL gives three functions to read "now", each with a different precision:

SELECT
  NOW()      AS now_datetime,     -- 2026-04-16 14:23:07
  CURDATE()  AS today,             -- 2026-04-16
  CURTIME()  AS time_only,         -- 14:23:07
  CURRENT_TIMESTAMP AS also_now;   -- same as NOW()

NOW() returns a DATETIME (date + time). CURDATE() returns only the DATE. CURTIME() returns only the TIME. All three read from the MySQL server clock, not the client. NOW() within a single query always returns the same value even if the query runs for minutes — use SYSDATE() if you actually want the clock to re-read.

2. DATEDIFF vs TIMESTAMPDIFF

These are the two functions people mix up most often. Both measure the distance between two dates, but they count different things.

-- DATEDIFF always returns an INTEGER number of DAYS
SELECT DATEDIFF('2026-04-16', '2026-04-10');  -- 6
SELECT DATEDIFF('2026-04-16', '2026-05-16');  -- -30 (can be negative)

-- TIMESTAMPDIFF takes a unit and is far more flexible
SELECT TIMESTAMPDIFF(DAY,   '2026-01-01', '2026-04-16');  -- 105
SELECT TIMESTAMPDIFF(MONTH, '2026-01-01', '2026-04-16');  -- 3
SELECT TIMESTAMPDIFF(YEAR,  '2008-04-16', '2026-04-16');  -- 18
SELECT TIMESTAMPDIFF(HOUR,  '2026-04-16 09:00', '2026-04-16 14:30');  -- 5

Rule of thumb: use DATEDIFF only for days and only when sign does not matter. Use TIMESTAMPDIFF for everything else, especially age calculation (YEAR between DOB and today) and tenure (MONTH between hire_date and today).

3. Adding and Subtracting Intervals

Never manually add days by doing date + 7. MySQL does not do what you want on DATE types. Use DATE_ADD, DATE_SUB, or the + INTERVAL shortcut:

SELECT DATE_ADD('2026-04-16', INTERVAL 7 DAY);    -- 2026-04-23
SELECT DATE_SUB('2026-04-16', INTERVAL 1 MONTH);  -- 2026-03-16
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);          -- 2026-04-16 16:23:07
SELECT CURDATE() + INTERVAL 30 DAY;               -- 2026-05-16
SELECT CURDATE() - INTERVAL 90 DAY;               -- 2026-01-16

Supported intervals: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. Month math respects calendar length — adding 1 month to Jan 31 gives Feb 28 (or 29 in a leap year), not Mar 3.

4. DATE_FORMAT — Display Dates the Way You Want

Databases store dates in ISO format (2026-04-16). When you need to show a date as "16th April, 2026" or "Thu 16 Apr 2:23 PM", use DATE_FORMAT with these codes:

SELECT DATE_FORMAT(NOW(), '%W, %d %M %Y');
-- 'Thursday, 16 April 2026'

SELECT DATE_FORMAT(NOW(), '%d-%m-%Y %h:%i %p');
-- '16-04-2026 02:23 PM'

SELECT DATE_FORMAT(NOW(), '%Y-%m');
-- '2026-04' (useful for GROUP BY month)

5. EXTRACT and the Shortcuts

To pull a single part out of a date, EXTRACT is the portable way, and YEAR(), MONTH(), DAY() are the MySQL shortcuts:

SELECT EXTRACT(YEAR   FROM '2026-04-16');    -- 2026
SELECT EXTRACT(MONTH  FROM '2026-04-16');    -- 4
SELECT EXTRACT(DAY    FROM '2026-04-16');    -- 16
SELECT EXTRACT(HOUR   FROM '2026-04-16 14:23:07');  -- 14

-- Shortcuts
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

-- Calendar helpers
SELECT DAYOFWEEK('2026-04-16');   -- 5 (Sunday=1, Thursday=5)
SELECT DAYNAME('2026-04-16');     -- 'Thursday'
SELECT MONTHNAME('2026-04-16');   -- 'April'
SELECT WEEK('2026-04-16');        -- 15 (ISO week number)
SELECT QUARTER('2026-04-16');     -- 2
SELECT DAYOFYEAR('2026-04-16');   -- 106

Warning on DAYOFWEEK: MySQL numbers Sunday as 1, Saturday as 7. Most of the world considers Monday the start of the week. Use WEEKDAY() instead, which numbers Monday=0, Sunday=6.

6. STR_TO_DATE — Parsing Strings Into Dates

When data arrives as a string (CSV uploads, API responses), you have to convert it. STR_TO_DATE is the inverse of DATE_FORMAT — same format codes, different direction:

SELECT STR_TO_DATE('16-04-2026', '%d-%m-%Y');
-- 2026-04-16 (as a proper DATE value)

SELECT STR_TO_DATE('April 16, 2026', '%M %d, %Y');
-- 2026-04-16

SELECT STR_TO_DATE('16/04/2026 02:23 PM', '%d/%m/%Y %h:%i %p');
-- 2026-04-16 14:23:00

If the string does not match the format, MySQL returns NULL. Always validate your import with a COUNT of NULLs before bulk-loading data.

7. LAST_DAY, TIMEDIFF, and Utility Functions

SELECT LAST_DAY('2026-04-16');   -- 2026-04-30 (last day of April)
SELECT LAST_DAY('2028-02-05');   -- 2028-02-29 (leap year)

-- TIMEDIFF returns a TIME value, good for same-day intervals
SELECT TIMEDIFF('14:30:00', '09:15:00');   -- 05:15:00

-- DATE() strips time off a DATETIME
SELECT DATE('2026-04-16 14:23:07');   -- 2026-04-16

-- TIME() strips date off a DATETIME
SELECT TIME('2026-04-16 14:23:07');   -- 14:23:07

-- UNIX_TIMESTAMP and FROM_UNIXTIME for epoch conversion
SELECT UNIX_TIMESTAMP('2026-04-16 14:23:07');  -- 1776695987
SELECT FROM_UNIXTIME(1776695987);               -- 2026-04-16 14:23:07

8. Time Zones and CONVERT_TZ

This is where junior devs lose hours of their life. MySQL has three relevant time zones: the system time zone (server OS), the global time zone (MySQL setting), and the session time zone (current connection).

-- Check what your session thinks
SELECT @@global.time_zone, @@session.time_zone;

-- Change for the current session
SET time_zone = '+05:30';   -- IST
SET time_zone = 'Asia/Kolkata';  -- named (requires tz tables loaded)

-- Convert a DATETIME from one zone to another
SELECT CONVERT_TZ('2026-04-16 14:23:07', '+00:00', '+05:30');
-- 2026-04-16 19:53:07  (UTC to IST)

SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00');
-- NOW in UTC

9. DATE vs DATETIME vs TIMESTAMP — The Decision

This is a common interview question. Pick based on what the column represents:

• DATE — only the calendar day matters. Date of birth, holiday date, invoice date.
• DATETIME — a specific moment that should never change with time zones. Scheduled webinar at 7 PM IST as displayed to the user.
• TIMESTAMP — audit-style fields like created_at and updated_at that represent a point in universal time. Stored as UTC internally, converted to the session TZ on read. Range limit: 1970 to 2038 (TIMESTAMP is 4 bytes).

CREATE TABLE orders (
  id           INT PRIMARY KEY,
  order_date   DATE NOT NULL,                -- customer-facing date
  placed_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- audit
  delivery_at  DATETIME                      -- scheduled moment
);

10. Common Patterns You Will Write 100 Times

-- 1. Orders in the last 7 days
SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY;

-- 2. Age from date of birth
SELECT name, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
FROM students;

-- 3. Revenue grouped by month
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

-- 4. Sign-ups per weekday
SELECT DAYNAME(created_at) AS day_of_week, COUNT(*) AS signups
FROM users
GROUP BY day_of_week
ORDER BY FIELD(day_of_week,'Monday','Tuesday','Wednesday',
               'Thursday','Friday','Saturday','Sunday');

-- 5. Yesterday's orders (avoid off-by-one)
SELECT * FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY;

-- 6. Customers inactive for 30+ days
SELECT user_id, MAX(last_login) AS last_seen
FROM sessions
GROUP BY user_id
HAVING DATEDIFF(CURDATE(), MAX(last_login)) >= 30;

Code Examples

-- Sample: no table needed
SELECT
  NOW()              AS current_moment,
  CURDATE()          AS today,
  CURTIME()          AS time_only,
  UNIX_TIMESTAMP()   AS epoch_seconds,
  DAYNAME(CURDATE()) AS today_weekday;

CREATE TABLE orders (
  id           INT PRIMARY KEY,
  customer     VARCHAR(50),
  order_date   DATE,
  amount       DECIMAL(10,2)
);

INSERT INTO orders VALUES
  (1, 'Aarav Sharma',   '2026-04-16',  1200.00),
  (2, 'Priya Nair',     '2026-04-15',   850.50),
  (3, 'Rohan Mehta',    '2026-04-13',  2400.00),
  (4, 'Ananya Iyer',    '2026-04-10',   999.00),
  (5, 'Vikram Singh',   '2026-04-05',  3100.00),
  (6, 'Meera Krishnan', '2026-03-20',   450.00);

-- Orders from the last 7 days (today is 2026-04-16)
SELECT id, customer, order_date, amount
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY
ORDER BY order_date DESC;

CREATE TABLE students (
  id     INT PRIMARY KEY,
  name   VARCHAR(50),
  dob    DATE
);

INSERT INTO students VALUES
  (1, 'Ishaan Gupta',   '2008-11-12'),
  (2, 'Kavya Reddy',    '2006-02-29'),   -- leap-day baby
  (3, 'Arjun Pillai',   '2010-04-17'),   -- birthday tomorrow
  (4, 'Neha Choudhary', '2010-04-15');   -- birthday yesterday

-- Exact age today (2026-04-16)
SELECT name,
       dob,
       TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
FROM students;

CREATE TABLE sales (
  id         INT PRIMARY KEY,
  sold_at    DATETIME,
  amount     DECIMAL(10,2)
);

INSERT INTO sales VALUES
  (1, '2026-01-10 10:22:00',  1200),
  (2, '2026-01-25 15:40:00',   800),
  (3, '2026-02-03 09:10:00',  2200),
  (4, '2026-02-28 18:05:00',  1500),
  (5, '2026-03-12 11:30:00',  3100),
  (6, '2026-03-29 20:00:00',   950),
  (7, '2026-04-05 13:15:00',  1800),
  (8, '2026-04-15 16:45:00',  2650);

SELECT DATE_FORMAT(sold_at, '%Y-%m') AS month,
       COUNT(*)                       AS orders,
       SUM(amount)                    AS revenue
FROM sales
GROUP BY month
ORDER BY month;

CREATE TABLE raw_upload (
  id          INT PRIMARY KEY,
  raw_date    VARCHAR(30)
);

INSERT INTO raw_upload VALUES
  (1, '16-04-2026'),
  (2, '05/04/2026'),
  (3, 'April 12, 2026'),
  (4, '2026.04.09');

SELECT id,
       raw_date,
       CASE
         WHEN raw_date LIKE '%-%'      THEN STR_TO_DATE(raw_date, '%d-%m-%Y')
         WHEN raw_date LIKE '%/%'      THEN STR_TO_DATE(raw_date, '%d/%m/%Y')
         WHEN raw_date LIKE '%.%'      THEN STR_TO_DATE(raw_date, '%Y.%m.%d')
         ELSE STR_TO_DATE(raw_date, '%M %d, %Y')
       END AS parsed_date
FROM raw_upload;

CREATE TABLE sessions (
  user_id      INT,
  last_login   DATETIME
);

INSERT INTO sessions VALUES
  (101, '2026-04-15 09:12:00'),
  (102, '2026-03-05 14:20:00'),   -- 42 days ago
  (103, '2026-04-10 11:00:00'),
  (104, '2026-01-20 08:30:00'),   -- 86 days ago
  (105, '2026-03-14 22:15:00');   -- 33 days ago

SELECT user_id,
       MAX(last_login)                          AS last_seen,
       DATEDIFF(CURDATE(), MAX(last_login))     AS days_inactive
FROM sessions
GROUP BY user_id
HAVING days_inactive >= 30
ORDER BY days_inactive DESC;

CREATE TABLE webinars (
  id          INT PRIMARY KEY,
  topic       VARCHAR(60),
  starts_utc  DATETIME   -- stored in UTC
);

INSERT INTO webinars VALUES
  (1, 'Python Masterclass', '2026-04-20 13:30:00'),
  (2, 'SQL Interview Prep', '2026-04-22 14:00:00'),
  (3, 'Web Dev Bootcamp',   '2026-04-25 03:00:00');

-- Show times to users in India (IST, +05:30) and New York (-04:00, DST)
SELECT topic,
       starts_utc                                          AS utc,
       CONVERT_TZ(starts_utc, '+00:00', '+05:30')          AS ist,
       CONVERT_TZ(starts_utc, '+00:00', '-04:00')          AS new_york,
       DATE_FORMAT(CONVERT_TZ(starts_utc,'+00:00','+05:30'),
                   '%W, %d %b %h:%i %p')                   AS display_ist
FROM webinars;

SELECT
  '2026-04-16'                                  AS d,
  LAST_DAY('2026-04-16')                        AS end_of_month,
  DAYOFYEAR('2026-04-16')                       AS day_of_year,
  QUARTER('2026-04-16')                         AS quarter,
  WEEKOFYEAR('2026-04-16')                      AS iso_week,
  DAYOFWEEK('2026-04-16')                       AS mysql_dow,     -- 1=Sun
  WEEKDAY('2026-04-16')                         AS iso_dow,       -- 0=Mon
  DATE_ADD(LAST_DAY('2026-04-16'), INTERVAL 1 DAY) AS first_of_next_month;

Common Mistakes

-- Kills any index on order_date
SELECT * FROM orders
WHERE YEAR(order_date) = 2026
  AND MONTH(order_date) = 4;

-- Use a range predicate instead
SELECT * FROM orders
WHERE order_date >= '2026-04-01'
  AND order_date <  '2026-05-01';

-- Intends to check 'was order more than 7 days ago?'
SELECT * FROM orders
WHERE DATEDIFF(order_date, CURDATE()) > 7;

-- DATEDIFF(later, earlier) or just use INTERVAL
SELECT * FROM orders
WHERE DATEDIFF(CURDATE(), order_date) > 7;
-- Or the cleaner version:
SELECT * FROM orders
WHERE order_date < CURDATE() - INTERVAL 7 DAY;

-- Months sorted alphabetically, not chronologically
SELECT DATE_FORMAT(sold_at, '%M %Y') AS month, SUM(amount)
FROM sales
GROUP BY month
ORDER BY month;

-- Group by sortable ISO format, then format for display
SELECT DATE_FORMAT(sold_at, '%Y-%m') AS month_key,
       DATE_FORMAT(MIN(sold_at), '%M %Y') AS month_label,
       SUM(amount) AS revenue
FROM sales
GROUP BY month_key
ORDER BY month_key;

CREATE TABLE bad_orders (
  id   INT PRIMARY KEY,
  date VARCHAR(20)   -- '16-04-2026' as a string
);

-- Sorting breaks completely
SELECT * FROM bad_orders ORDER BY date;
-- '01-02-2026' comes before '16-04-2026' alphabetically,
-- but also before '05-01-2026', which is wrong chronologically.

CREATE TABLE orders (
  id         INT PRIMARY KEY,
  order_date DATE   -- proper type
);
-- Sorting, ranges, and all date functions now work correctly.

-- Table has DATETIME, server moved from IST to UTC
CREATE TABLE logs (event_at DATETIME);
INSERT INTO logs VALUES (NOW());
-- Months later, server is moved to UTC.
-- Old values look 5h30 off, but they are just stored as raw strings.

-- Use TIMESTAMP for audit timestamps
CREATE TABLE logs (
  event_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- TIMESTAMP is stored as UTC internally and always
-- re-interpreted in the session's current time zone.

Summary