Date Functions

CREATE TABLE class_sessions (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    subject      VARCHAR(60),
    session_date DATE,      -- 2026-08-14, no time component at all
    starts_at    TIME,      -- 09:00:00, a wall-clock time
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- a real moment
    updated_at   DATETIME                              -- stored as written
);

-- Date literals are written year-month-day: unambiguous, and sortable
INSERT INTO class_sessions (subject, session_date, starts_at)
VALUES ('Databases', '2026-08-14', '09:00:00');

-- Never do this
-- CREATE TABLE bad_idea (session_date VARCHAR(20));
-- '14/08/2026' cannot be sorted, ranged over, or added to,
-- and '31/02/2026' would be accepted without a murmur.

-- Converting text into a real date
SELECT DATE('2026-08-14 09:30:00') AS just_the_date;
SELECT CAST('2026-08-14' AS DATE)  AS parsed;

-- The basics
SELECT NOW()             AS date_and_time;
SELECT CURDATE()         AS today;
SELECT CURTIME()         AS time_now;
SELECT CURRENT_TIMESTAMP AS portable_now;   -- MySQL and PostgreSQL

-- What time zone is this server actually running in?
SELECT NOW() AS server_time, UTC_TIMESTAMP() AS utc_time;
SELECT @@global.time_zone, @@session.time_zone;

-- Set the current connection to IST (MySQL)
SET time_zone = '+05:30';

-- Convert a stored UTC value for display (MySQL)
SELECT id, CONVERT_TZ(ordered_at, '+00:00', '+05:30') AS ordered_at_ist
FROM orders;

-- Let the database fill the columns in for you
CREATE TABLE enquiries (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                         ON UPDATE CURRENT_TIMESTAMP
);

-- MySQL: one function per part
SELECT
    joined_on,
    YEAR(joined_on)    AS y,
    MONTH(joined_on)   AS m,
    DAY(joined_on)     AS d,
    QUARTER(joined_on) AS q,
    DAYNAME(joined_on) AS day_name
FROM students;

-- The portable form: MySQL, PostgreSQL and Oracle
SELECT
    EXTRACT(YEAR  FROM joined_on) AS y,
    EXTRACT(MONTH FROM joined_on) AS m
FROM students;

-- Two day-of-week functions in one database, numbered differently.
-- 2026-08-14 is a Friday.
SELECT
    DAYOFWEEK('2026-08-14') AS dayofweek,   -- 6, where 1 = Sunday
    WEEKDAY('2026-08-14')   AS weekday,     -- 4, where 0 = Monday
    DAYNAME('2026-08-14')   AS day_name;    -- 'Friday'

-- Easier to read, and much harder to get wrong
SELECT id, ordered_at
FROM orders
WHERE DAYNAME(ordered_at) IN ('Saturday', 'Sunday');

-- Enrolments per month
SELECT
    EXTRACT(YEAR  FROM joined_on) AS y,
    EXTRACT(MONTH FROM joined_on) AS m,
    COUNT(*)                      AS students
FROM students
GROUP BY EXTRACT(YEAR FROM joined_on), EXTRACT(MONTH FROM joined_on)
ORDER BY y, m;

-- Adding and subtracting (MySQL)
SELECT
    joined_on,
    DATE_ADD(joined_on, INTERVAL 30 DAY)  AS plus_30_days,
    DATE_SUB(joined_on, INTERVAL 1 MONTH) AS minus_1_month,
    joined_on + INTERVAL 1 YEAR           AS next_year
FROM students;

-- Calendars, not counting: 31 Jan plus one month is 28 Feb in 2026
SELECT
    DATE_ADD('2026-01-31', INTERVAL 1 MONTH) AS add_a_month,  -- 2026-02-28
    DATE_ADD('2026-01-31', INTERVAL 30 DAY)  AS add_30_days;  -- 2026-03-02

-- MySQL: two dates in, days out, computed as the first minus the second
SELECT name, DATEDIFF(CURDATE(), joined_on) AS days_since_joining
FROM students;

-- MySQL: any unit you like, but the arguments are the other way round
SELECT
    TIMESTAMPDIFF(DAY,   joined_on, CURDATE()) AS days,
    TIMESTAMPDIFF(MONTH, joined_on, CURDATE()) AS months,
    TIMESTAMPDIFF(YEAR,  joined_on, CURDATE()) AS years
FROM students;

-- Age, done properly: completed years, leap years handled
SELECT name, dob, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age
FROM students;

-- Age, done wrongly: drifts by a day every four years
-- SELECT name, DATEDIFF(CURDATE(), dob) / 365 AS age FROM students;

-- Other dialects
-- PostgreSQL: joined_on + INTERVAL '30 days'
--             AGE(CURRENT_DATE, dob)
-- SQL Server: DATEADD(day, 30, joined_on)
--             DATEDIFF(day, joined_on, GETDATE())

-- Correct but slow: the function hides the column from its own index
SELECT id, ordered_at FROM orders WHERE YEAR(ordered_at) = 2026;

-- Correct and fast: one contiguous slice of the index
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= '2026-01-01'
  AND ordered_at <  '2027-01-01';

-- A single month, written exactly the same way
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= '2026-06-01'
  AND ordered_at <  '2026-07-01';

-- The BETWEEN bug on a DATETIME column: everything after midnight
-- on 30 June is silently excluded
-- SELECT id FROM orders
-- WHERE ordered_at BETWEEN '2026-06-01' AND '2026-06-30';

-- Relative windows still use the index, because the function is
-- applied to a constant rather than to the column
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);

-- Today's orders, with no function anywhere near the column
SELECT id, ordered_at
FROM orders
WHERE ordered_at >= CURDATE()
  AND ordered_at <  CURDATE() + INTERVAL 1 DAY;

-- MySQL
SELECT
    joined_on,
    DATE_FORMAT(joined_on, '%d %b %Y')          AS friendly,
    DATE_FORMAT(joined_on, '%W, %d %M %Y')      AS long_form,
    DATE_FORMAT(joined_on, '%Y-%m')             AS month_key,
    DATE_FORMAT(NOW(),     '%d/%m/%Y %h:%i %p') AS with_time
FROM students;

-- %m is the month and %i is the minute. This is the usual slip:
--   DATE_FORMAT(NOW(), '%H:%m')   -- hour and MONTH, not hour and minute
--   DATE_FORMAT(NOW(), '%H:%i')   -- what was actually wanted

-- Sorting a formatted date only works when it is year-first
SELECT DATE_FORMAT(joined_on, '%Y-%m') AS month_key, COUNT(*) AS students
FROM students
GROUP BY DATE_FORMAT(joined_on, '%Y-%m')
ORDER BY month_key;

-- An ISO string, for when a program will read the value
SELECT DATE_FORMAT(joined_on, '%Y-%m-%d') AS iso_date FROM students;

-- Other dialects
-- PostgreSQL and Oracle: TO_CHAR(joined_on, 'DD Mon YYYY')
-- SQL Server:            FORMAT(joined_on, 'dd MMM yyyy')