#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

MySQL Date and Time Functions: Complete Guide

Most date work in MySQL comes down to eight questions: what is the date now, how far apart are two dates, what date is N days later, how do I show a date nicely, how do I read a date typed as text, which part of the date do I need, how do I group by month, and how do I filter a range safely. This page answers each one with a query you can run and the exact output it returns.

The sample table

CREATE TABLE bookings (
  booking_id INT PRIMARY KEY,
  guest      VARCHAR(20),
  booked_at  DATETIME,
  check_in   DATE,
  check_out  DATE
);
INSERT INTO bookings VALUES
(1,'Ritu', '2026-10-28 09:15:00','2026-11-14','2026-11-17'),
(2,'Kabir','2026-11-02 22:40:00','2026-11-20','2026-11-21'),
(3,'Sana', '2026-11-10 13:05:00','2026-12-30','2027-01-02');

Three types appear here. DATE holds a day, DATETIME holds a day and a time, and TIMESTAMP (not used here) also holds both but converts to and from the session time zone. The MySQL data types guide compares them.

1. What is the current date and time?

NOW() returns the date and time, CURDATE() the date only, and CURTIME() the time only. All three use the session time zone. NOW() returns the same value for every row in one statement. SYSDATE() returns the moment each call actually runs. The results depend on when you run them, so no fixed output is shown.

2. How many days or months between two dates?

SELECT guest,
       DATEDIFF(check_out, check_in)        AS nights,
       DATEDIFF(check_in, DATE(booked_at))  AS days_in_advance
FROM bookings;
guestnightsdays_in_advance
Ritu317
Kabir118
Sana350

DATEDIFF(a, b) returns a - b in whole days and ignores the time part. For other units, use TIMESTAMPDIFF(unit, start, end). Note that the argument order is reversed compared with DATEDIFF:

SELECT TIMESTAMPDIFF(MONTH, '2025-06-15', '2026-11-16') AS months,
       TIMESTAMPDIFF(YEAR,  '2001-08-20', '2026-11-16') AS age_years,
       TIMESTAMPDIFF(HOUR,  '2026-11-15 22:00:00', '2026-11-16 06:30:00') AS hours;
monthsage_yearshours
17258

It counts complete units only: 8 hours 30 minutes returns 8. That makes it the right way to calculate age from a date of birth.

3. What date is it N days or months later?

SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH) AS plus_month,
       DATE_SUB('2026-03-01', INTERVAL 1 DAY)   AS day_before,
       '2026-11-16' + INTERVAL 45 DAY           AS plus_45,
       LAST_DAY('2028-02-10')                   AS feb_end;
plus_monthday_beforeplus_45feb_end
2026-02-282026-02-282026-12-312028-02-29

Adding a month to 31 January gives 28 February: MySQL moves to the last valid day instead of spilling into March. LAST_DAY() handles leap years (2028 is one). That is handy for "bill on the last day of the month" logic.

4. How do I display a date the Indian way?

SELECT guest,
       DATE_FORMAT(booked_at, '%d %b %Y, %h:%i %p') AS booked_on,
       DATE_FORMAT(check_in, '%W')                  AS check_in_day
FROM bookings;
guestbooked_oncheck_in_day
Ritu28 Oct 2026, 09:15 AMSaturday
Kabir02 Nov 2026, 10:40 PMFriday
Sana10 Nov 2026, 01:05 PMWednesday
SpecifierMeaningSpecifierMeaning
%dday 01–31%Hhour 00–23
%mmonth 01–12%hhour 01–12
%b / %MOct / October%iminutes (not %M)
%Y / %y2026 / 26%pAM / PM
%W / %aSaturday / Sat%sseconds

A common slip is to use %M for minutes. It prints the month name.

5. How do I turn "16/11/2026" into a real date?

SELECT STR_TO_DATE('16/11/2026', '%d/%m/%Y')             AS from_indian_format,
       STR_TO_DATE('16-11-2026 18:45', '%d-%m-%Y %H:%i') AS with_time,
       STR_TO_DATE('31/02/2026', '%d/%m/%Y')             AS invalid;
from_indian_formatwith_timeinvalid
2026-11-162026-11-16 18:45:00NULL

Use this when you load CSV files typed in DD/MM/YYYY format. An impossible date returns NULL with a warning instead of a wrong date, so check for NULLs after an import. Loading such files is covered in importing CSV data into MySQL.

6. How do I pull out the year, month or hour?

SELECT guest, YEAR(check_in) AS y, MONTH(check_in) AS m, DAY(check_in) AS d,
       EXTRACT(HOUR FROM booked_at) AS booked_hour, QUARTER(check_in) AS q
FROM bookings;
guestymdbooked_hourq
Ritu2026111494
Kabir20261120224
Sana20261230134

Watch the weekday functions. For Monday 16 November 2026, WEEKDAY() returns 0 (Monday = 0) but DAYOFWEEK() returns 2 (Sunday = 1). DAYNAME() returns 'Monday'. QUARTER() follows the calendar year; for an April–March financial year, compute the quarter yourself.

7. How do I group by month?

SELECT DATE_FORMAT(booked_at, '%Y-%m') AS mon, COUNT(*) AS bookings
FROM bookings
GROUP BY mon
ORDER BY mon;
monbookings
2026-101
2026-112

Use '%Y-%m', not '%b': the year-month string sorts correctly and keeps October 2026 apart from October 2027. More grouping patterns are in the GROUP BY clause explained.

8. How do I filter a date range without missing rows?

SELECT guest FROM bookings
WHERE booked_at BETWEEN '2026-10-28' AND '2026-11-10';
guest
Ritu
Kabir

Sana booked on 10 November, yet she is missing. '2026-11-10' means midnight at the start of that day, so 13:05 falls outside the range. Use a half-open range instead. It is also index-friendly, unlike WHERE MONTH(booked_at) = 11:

SELECT guest FROM bookings
WHERE booked_at >= '2026-11-01' AND booked_at < '2026-12-01';
guest
Kabir
Sana

Time zones

Servers often run in UTC while users are in IST. Offsets work without extra setup:

SELECT CONVERT_TZ('2026-11-16 04:30:00', '+00:00', '+05:30') AS ist;
ist
2026-11-16 10:00:00

Named zones such as 'Asia/Kolkata' only work after the time zone tables are loaded on the server. Without them, CONVERT_TZ returns NULL. India has no daylight saving time, so a fixed +05:30 offset is safe for IST.

For text manipulation alongside dates, see MySQL string functions.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →