SQL Date Functions Cheat Sheet — MySQL vs PostgreSQL

Date handling is one of the biggest sources of "why doesn't this work" moments when switching between databases — the concepts are the same, but the function names and syntax differ. This cheat sheet lines up the most common date operations — getting the current date, adding or subtracting time, finding the difference between dates, formatting output, and extracting parts — side by side for MySQL and PostgreSQL.

Current Date and Time

-- MySQL
SELECT NOW();          -- current date + time: 2026-07-11 14:30:00
SELECT CURDATE();      -- current date only: 2026-07-11
SELECT CURTIME();      -- current time only: 14:30:00

-- PostgreSQL
SELECT NOW();                 -- current date + time (with timezone)
SELECT CURRENT_TIMESTAMP;     -- same as NOW()
SELECT CURRENT_DATE;          -- current date only: 2026-07-11
SELECT CURRENT_TIME;          -- current time only

Adding and Subtracting Dates

Both databases support interval arithmetic, but the syntax shape differs:

-- MySQL: add/subtract with DATE_ADD / DATE_SUB, or shorthand +/- INTERVAL
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH) FROM orders;
SELECT order_date + INTERVAL 7 DAY FROM orders;   -- shorthand, same result

-- PostgreSQL: date/timestamp arithmetic with INTERVAL directly
SELECT order_date + INTERVAL '7 days' FROM orders;
SELECT order_date - INTERVAL '1 month' FROM orders;

Difference Between Two Dates

-- MySQL: DATEDIFF returns whole days between two dates
SELECT DATEDIFF(shipped_date, order_date) AS days_to_ship
FROM orders;

-- For finer granularity, use TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(HOUR, order_date, shipped_date) AS hours_to_ship
FROM orders;

-- PostgreSQL: subtracting two dates returns an integer number of days
SELECT (shipped_date - order_date) AS days_to_ship
FROM orders;

-- For timestamps, subtraction returns an INTERVAL; extract a unit with EXTRACT
SELECT EXTRACT(EPOCH FROM (shipped_at - order_at)) / 3600 AS hours_to_ship
FROM orders;

Formatting Dates for Display

-- MySQL: DATE_FORMAT with %-prefixed codes
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_date FROM orders;
SELECT DATE_FORMAT(order_date, '%d %b %Y') AS pretty_date FROM orders;
-- Example output: 11 Jul 2026

-- PostgreSQL: TO_CHAR with named format patterns
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_date FROM orders;
SELECT TO_CHAR(order_date, 'DD Mon YYYY') AS pretty_date FROM orders;
-- Example output: 11 Jul 2026

Common format codes side by side:

• 4-digit year: MySQL %Y — PostgreSQL YYYY
• 2-digit month: MySQL %m — PostgreSQL MM
• 2-digit day: MySQL %d — PostgreSQL DD
• 24-hour hour: MySQL %H — PostgreSQL HH24
• Minutes: MySQL %i — PostgreSQL MI

Extracting Parts of a Date

EXTRACT() is standard SQL and works in both databases, though MySQL also offers dedicated shortcut functions:

-- MySQL: EXTRACT works, plus dedicated functions
SELECT EXTRACT(YEAR FROM order_date) AS yr FROM orders;
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date) FROM orders;
SELECT DAYNAME(order_date) AS weekday FROM orders;   -- 'Saturday'

-- PostgreSQL: EXTRACT is the standard approach
SELECT EXTRACT(YEAR FROM order_date) AS yr FROM orders;
SELECT EXTRACT(MONTH FROM order_date) AS mo FROM orders;
SELECT TO_CHAR(order_date, 'Day') AS weekday FROM orders;  -- 'Saturday '

Filtering by Date Range — A Portable Pattern

Regardless of dialect, comparing against explicit boundary dates is the most reliable and index-friendly way to filter by date, since it avoids wrapping the indexed column in a function:

-- Works the same in MySQL and PostgreSQL
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01';

-- Avoid this -- wrapping the column in a function blocks index usage:
-- WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 1

Quick Reference Table

1. Current timestamp: MySQL NOW() — PostgreSQL NOW() / CURRENT_TIMESTAMP
2. Current date only: MySQL CURDATE() — PostgreSQL CURRENT_DATE
3. Add interval: MySQL DATE_ADD(d, INTERVAL n UNIT) — PostgreSQL d + INTERVAL 'n unit'
4. Date difference (days): MySQL DATEDIFF(d1, d2) — PostgreSQL d1 - d2
5. Format for display: MySQL DATE_FORMAT(d, fmt) — PostgreSQL TO_CHAR(d, fmt)
6. Extract a part: MySQL EXTRACT(unit FROM d) or YEAR(d) — PostgreSQL EXTRACT(unit FROM d)

Frequently Asked Questions