SQL Date & Time Functions – NOW, DATEDIFF, DATE_FORMAT

Current Date and Time

SQL provides functions to get the current date and time from the database server — not from the client application. This ensures consistency when multiple applications write to the same database.

SQL

-- Full timestamp (date + time)
SELECT NOW();               -- '2026-06-08 14:30:00' (MySQL / PostgreSQL)
SELECT CURRENT_TIMESTAMP;   -- same, ANSI SQL standard

-- Date only
SELECT CURDATE();           -- '2026-06-08' (MySQL)
SELECT CURRENT_DATE;        -- '2026-06-08' (ANSI / PostgreSQL)

-- Time only
SELECT CURTIME();           -- '14:30:00' (MySQL)
SELECT CURRENT_TIME;        -- '14:30:00' (ANSI)

-- Practical: stamp records on insert
INSERT INTO audit_log (event, created_at)
VALUES ('user_login', NOW());

-- Filter records from today
SELECT * FROM orders
WHERE DATE(created_at) = CURDATE();

💡

Store dates in UTC

Always store timestamps in UTC in the database. Use UTC_TIMESTAMP() (MySQL) or NOW() AT TIME ZONE 'UTC' (PostgreSQL) when inserting. Convert to local time zones only for display purposes. This avoids daylight saving time bugs and makes cross-timezone queries consistent.

EXTRACT()

EXTRACT(part FROM date) pulls out a specific component of a date. It is ANSI SQL and works in both MySQL and PostgreSQL.

SQL

SELECT EXTRACT(YEAR  FROM '2026-06-08');   -- 2026
SELECT EXTRACT(MONTH FROM '2026-06-08');   -- 6
SELECT EXTRACT(DAY   FROM '2026-06-08');   -- 8
SELECT EXTRACT(HOUR  FROM NOW());          -- current hour
SELECT EXTRACT(DOW   FROM NOW());          -- day of week (PostgreSQL: 0=Sunday)

-- MySQL shortcuts (non-standard but common)
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date)
FROM orders;

-- Group sales by month and year
SELECT
  EXTRACT(YEAR  FROM order_date) AS yr,
  EXTRACT(MONTH FROM order_date) AS mo,
  SUM(total_amount)              AS monthly_revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

Date Arithmetic

Add or subtract time intervals from dates using DATE_ADD / DATE_SUB (MySQL) or the INTERVAL keyword (PostgreSQL).

SQL

-- MySQL
SELECT DATE_ADD('2026-06-08', INTERVAL 30 DAY);    -- '2026-07-08'
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);           -- one month from now
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);             -- 7 days ago

-- PostgreSQL
SELECT '2026-06-08'::date + INTERVAL '30 days';
SELECT NOW() - INTERVAL '7 days';

-- ANSI (works in many databases)
SELECT CURRENT_DATE + INTERVAL '30' DAY;

-- Practical: find orders placed in the last 30 days
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Find subscriptions expiring within the next 7 days
SELECT subscription_id, user_id, expires_at
FROM subscriptions
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY);

Date Formatting

Format dates as strings for display or reporting. MySQL uses DATE_FORMAT(); PostgreSQL uses TO_CHAR().

SQL

-- MySQL: DATE_FORMAT(date, format_string)
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');         -- '2026-06-08'
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');         -- '08/06/2026'
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');        -- 'June 08, 2026'
SELECT DATE_FORMAT(NOW(), '%W');               -- 'Monday' (day name)
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');         -- '14:30:00'

-- PostgreSQL: TO_CHAR(date, format_string)
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');           -- '2026-06-08'
SELECT TO_CHAR(NOW(), 'DD/MM/YYYY');           -- '08/06/2026'
SELECT TO_CHAR(NOW(), 'Month DD, YYYY');       -- 'June      08, 2026'
SELECT TO_CHAR(NOW(), 'HH24:MI:SS');           -- '14:30:00'

-- Convert string to date
SELECT STR_TO_DATE('08/06/2026', '%d/%m/%Y');  -- MySQL: '2026-06-08'
SELECT TO_DATE('08/06/2026', 'DD/MM/YYYY');    -- PostgreSQL

DATEDIFF()

Calculate the number of days between two dates.

SQL

-- MySQL: DATEDIFF(date1, date2) → date1 minus date2 in days
SELECT DATEDIFF('2026-07-08', '2026-06-08');   -- 30
SELECT DATEDIFF(NOW(), '2025-01-01');           -- days since 2025-01-01

-- PostgreSQL: subtract dates directly
SELECT '2026-07-08'::date - '2026-06-08'::date;  -- 30 (integer)
SELECT NOW()::date - '2025-01-01'::date;          -- days since

-- Calculate customer tenure in days
SELECT
  customer_id,
  DATEDIFF(CURDATE(), created_at) AS days_as_customer
FROM customers
ORDER BY days_as_customer DESC;

-- Find how many days until a subscription expires
SELECT
  subscription_id,
  DATEDIFF(expires_at, CURDATE()) AS days_remaining
FROM subscriptions
WHERE expires_at > CURDATE();

DATE_TRUNC() (PostgreSQL)

DATE_TRUNC(precision, timestamp) truncates a timestamp to the given precision. This is a PostgreSQL-specific function invaluable for grouping data by week, month, quarter, or year.

SQL

-- PostgreSQL DATE_TRUNC
SELECT DATE_TRUNC('month', NOW());   -- '2026-06-01 00:00:00'
SELECT DATE_TRUNC('year',  NOW());   -- '2026-01-01 00:00:00'
SELECT DATE_TRUNC('week',  NOW());   -- start of current week (Monday)
SELECT DATE_TRUNC('day',   NOW());   -- today at midnight

-- Group revenue by month (PostgreSQL)
SELECT
  DATE_TRUNC('month', order_date) AS month_start,
  SUM(total_amount)               AS monthly_revenue
FROM orders
GROUP BY month_start
ORDER BY month_start;

-- MySQL equivalent: use DATE_FORMAT to group by month
SELECT
  DATE_FORMAT(order_date, '%Y-%m-01') AS month_start,
  SUM(total_amount)                   AS monthly_revenue
FROM orders
GROUP BY month_start
ORDER BY month_start;

Summary

📋 Summary

NOW() / CURRENT_TIMESTAMP — Current date and time from the server.
CURDATE() / CURRENT_DATE — Today's date (no time).
EXTRACT(part FROM date) — Pull out year, month, day, hour, etc. ANSI standard.
DATE_ADD(date, INTERVAL n unit) — Add time to a date. MySQL. PostgreSQL uses + INTERVAL.
DATEDIFF(d1, d2) — Days between two dates (MySQL). PostgreSQL: d1 - d2.
DATE_FORMAT() / TO_CHAR() — Format a date as a string. MySQL / PostgreSQL respectively.
DATE_TRUNC(precision, ts) — Truncate to month/week/year. PostgreSQL only.
Best practice — Store all timestamps in UTC. Convert to local time for display only.

FAQ

What is the difference between NOW() and CURDATE()?+

NOW() returns both the date and time (e.g. 2026-06-08 14:30:00). CURDATE() returns only the date (e.g. 2026-06-08). When filtering records for "today", prefer comparing with CURDATE() or wrapping with DATE() to avoid time component issues: WHERE DATE(created_at) = CURDATE().

How do I calculate the number of months between two dates?+

MySQL provides TIMESTAMPDIFF(MONTH, start_date, end_date). PostgreSQL: EXTRACT(YEAR FROM AGE(end_date, start_date)) * 12 + EXTRACT(MONTH FROM AGE(end_date, start_date)). For approximate months, DATEDIFF(end, start) / 30.0 works across databases but is imprecise.

Why should I store dates in UTC?+

Storing dates in UTC prevents bugs caused by daylight saving time transitions, avoids ambiguity when servers are in different timezones, and makes it straightforward to convert to any local timezone at query time. Once you store in local time, correcting it later is painful and risky.

How do I filter records from the current month?+

MySQL: WHERE YEAR(order_date) = YEAR(CURDATE()) AND MONTH(order_date) = MONTH(CURDATE()). PostgreSQL: WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE). An even simpler approach that works in both: WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') (MySQL) or WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) (PostgreSQL).