Date Functions

MySQL date/time functions, with outputs you can trust

Dates are a common source of silently-wrong reports, so here are the core functions with verified results. "Now" comes in three resolutions:

NOW()      -- 2024-03-09 14:32:05   (date + time)
CURDATE()  -- 2024-03-09            (date only)
CURTIME()  -- 14:32:05              (time only)

Formatting: DATE_FORMAT(date, format)

The format string uses % specifiers. The case matters — %m is a zero-padded month number, %M is the month name:

Specifier	Means	Example (2024-03-09)
%Y / %y	year 4-digit / 2-digit	2024 / 24
%m / %M	month number / name	03 / March
%d / %D	day number / ordinal	09 / 9th
%H:%i:%s	hour:min:sec	14:32:05
%W	weekday name	Saturday

DATE_FORMAT('2024-03-09', '%d/%m/%Y')      -- '09/03/2024'
DATE_FORMAT('2024-03-09', '%W, %M %D %Y')  -- 'Saturday, March 9th 2024'

Arithmetic: differences and offsets

DATEDIFF('2024-03-10', '2024-03-01')          -- 9   (arg1 - arg2, in days)
DATE_ADD('2024-03-09', INTERVAL 7 DAY)        -- '2024-03-16'
DATE_SUB('2024-03-09', INTERVAL 1 MONTH)      -- '2024-02-09'
TIMESTAMPDIFF(YEAR, '1990-05-01', '2024-03-09') -- 33   (whole years)
YEAR('2024-03-09'), MONTH(...), DAY(...)      -- 2024, 3, 9

Cross-engine map (MySQL ↔ Postgres)

Task	MySQL	PostgreSQL
Current timestamp	NOW(), CURRENT_TIMESTAMP	same
Format for display	DATE_FORMAT(ts, '%Y-%m')	to_char(ts, 'YYYY-MM')
Truncate to day/month	DATE(ts), DATE_FORMAT(ts,'%Y-%m-01')	date_trunc('day', ts), date_trunc('month', ts)
Add interval	DATE_ADD(ts, INTERVAL 7 DAY)	ts + INTERVAL '7 days'
Difference in days	DATEDIFF(a,b)	a::date - b::date
Extract year	YEAR(ts)	EXTRACT(YEAR FROM ts)

date_trunc is the Postgres workhorse for grouping ("orders per month"): group by date_trunc('month', created_at) rather than formatting to a string.

TIMESTAMP vs TIMESTAMPTZ (and MySQL's DATETIME)

• PostgreSQL timestamp without time zone (TIMESTAMP): stores a wall-clock value with no zone. The session TimeZone setting does not reinterpret it on read. Good for "local civil time that must not shift" (e.g. a store's posted hours) — dangerous if you pretend it is UTC.
• PostgreSQL timestamp with time zone (TIMESTAMPTZ): stores an absolute instant (internally UTC). On input/output, converts using the session TimeZone. Prefer TIMESTAMPTZ for events ("order placed at").
• MySQL DATETIME: wall-clock, no zone — similar spirit to TIMESTAMP without time zone.
• MySQL TIMESTAMP: stored UTC, converted to/from time_zone session variable on read/write. Range historically limited; many teams use DATETIME + explicit UTC convention instead.

-- Postgres: store instants as timestamptz
created_at TIMESTAMPTZ NOT NULL DEFAULT now();

-- Filter a UTC day correctly (half-open):
WHERE created_at >= TIMESTAMPTZ '2024-03-09 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2024-03-10 00:00:00+00';

DST and "add one day" traps

Civil time is not uniform. In US zones that observe DST, "add 24 hours" and "add 1 calendar day" diverge on spring-forward/fall-back nights. DATE_ADD(ts, INTERVAL 1 DAY) / Postgres ts + INTERVAL '1 day' follow calendar arithmetic on timestamp types in ways that can jump wall clocks; for absolute durations prefer interval hours on timestamptz and convert for display only. Never implement "business day" logic as raw hour arithmetic without a calendar library.

Pitfalls

• DATEDIFF counts calendar days, ignoring the time part; TIMESTAMPDIFF lets you ask for whole years/months/hours — use it for ages and durations.
• Don't wrap an indexed date column in a function in a WHERE clause (WHERE YEAR(created) = 2024) — it defeats the index. Use a range instead: WHERE created >= '2024-01-01' AND created < '2025-01-01'.
• Mixing TIMESTAMP and TIMESTAMPTZ without an explicit zone produces silent off-by-hours bugs at DST boundaries.
• Formatting (DATE_FORMAT/to_char) is for display — do not group by formatted strings if you can group by truncated timestamps.

Takeaways

• %m = month number, %M = month name; case changes the meaning.
• DATEDIFF = days (arg1−arg2); TIMESTAMPDIFF(unit,…) = whole units; DATE_ADD/SUB + INTERVAL for offsets.
• Keep functions off indexed date columns in WHERE — filter by a half-open range.
• Prefer TIMESTAMPTZ (absolute instants) for events; know DATETIME vs TIMESTAMP in MySQL; use date_trunc in Postgres.
• DST makes "add one day" ≠ "add 24 hours" — decide civil vs absolute time deliberately.

---

Re-authored for correctness and cross-engine depth for this guide (the prior version had a repeated DATE_FORMAT error; timezone/DST/Postgres map added). Per the MySQL date-and-time functions reference and PostgreSQL datetime docs. See also: WHERE Clause, Indexes (sargability), SQL Fundamentals semantic gotchas (timezone deep dive).

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Date Functions

Why this concept exists (judgment chain)

Date bugs are silent: wrong format case, non-sargable YEAR(col), wall-clock vs absolute time, DST “add one day.” Cross-engine maps and half-open ranges are production hygiene, not trivia.

Worked example with numbers or traced steps

DATE_FORMAT('2024-03-09','%d/%m/%Y') → 09/03/2024
DATEDIFF('2024-03-10','2024-03-01') → 9 calendar days
Sargable year filter:
  created >= '2024-01-01' AND created < '2025-01-01'
  -- not WHERE YEAR(created)=2024
Postgres events: TIMESTAMPTZ + date_trunc('month', created_at)
DST: INTERVAL '1 day' ≠ 24 hours on spring-forward nights.

When NOT to use / named alternative

Do not store local wall times as UTC without documenting zone. Do not GROUP BY DATE_FORMAT strings when date_trunc/DATE keeps order and type. Avoid business-day logic with raw hour arithmetic — use a calendar library. Prefer TIMESTAMPTZ for events; TIMESTAMP-without-tz only for zone-free civil schedules.

Failure / ops fingerprint

Fingerprint: index ignored on YEAR(created); reports off by one day near DST; MySQL TIMESTAMP converting via session time_zone unexpectedly. Ops: set DB and app to UTC; half-open ranges in all daily jobs; monitor session time_zone drift.

Hostile-panel drills (defend the decision)

Q1. %m vs %M in MySQL DATE_FORMAT?
Model answer: %m is zero-padded month number (03); %M is month name (March). Case changes meaning.

Q2. Why half-open ranges for a day?
Model answer: >= start AND < next_day includes full day, avoids 23:59:59 leap/precision bugs, stays sargable.

Q3. TIMESTAMP vs TIMESTAMPTZ in Postgres?
Model answer: TIMESTAMP is wall-clock without zone; TIMESTAMPTZ stores absolute instants (UTC internally). Prefer TIMESTAMPTZ for “when did this happen.”

I just read a lesson on **Date Functions** (Databases) and want to truly understand it. Explain Date Functions from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Date Functions** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Date Functions** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Date Functions** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.