Date Functions (NOW, DATE, DATEDIFF)
Date and time functions are critical for analytics, reporting, and scheduled operations. Extract date parts, calculate differences, format dates for display, and filter by time ranges.
Date Function Reference
- NOW() / CURRENT_TIMESTAMP - current date and time
- CURRENT_DATE - today's date only
- DATE(datetime) - extract date portion from datetime
- EXTRACT(YEAR/MONTH/DAY FROM col) - get date part (PostgreSQL)
- DATE_FORMAT(col, format) - format date as string (MySQL)
- TO_CHAR(col, format) - format date as string (PostgreSQL)
- DATEDIFF(d1, d2) - difference in days (MySQL: DATEDIFF(later, earlier))
- AGE(timestamp) - human-readable age (PostgreSQL)
- DATE_TRUNC('month', col) - truncate to month boundary (PostgreSQL)
- INTERVAL - add/subtract time: NOW() + INTERVAL '30 days'
Date Function Examples
-- Current time and today's date
SELECT NOW() AS current_datetime, CURRENT_DATE AS today;
-- Extract parts from order dates
SELECT
id,
created_at,
EXTRACT(YEAR FROM created_at) AS year,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(HOUR FROM created_at) AS hour
FROM orders;
-- Format for display (PostgreSQL)
SELECT TO_CHAR(created_at, 'DD Mon YYYY') AS formatted FROM orders;
-- Result: '15 Jan 2024'
-- Format (MySQL)
SELECT DATE_FORMAT(created_at, '%d %b %Y') AS formatted FROM orders;
-- Days since order was placed
SELECT id, CURRENT_DATE - created_at::date AS days_old FROM orders; -- PG
-- MySQL: SELECT id, DATEDIFF(CURDATE(), created_at) AS days_old FROM orders;
-- Orders from the last 30 days
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days';
-- MySQL: WHERE created_at >= NOW() - INTERVAL 30 DAY
-- Monthly revenue (truncate to month)
SELECT
DATE_TRUNC('month', paid_at) AS month, -- PostgreSQL
SUM(amount) AS revenue
FROM payments
WHERE paid_at IS NOT NULL
GROUP BY DATE_TRUNC('month', paid_at)
ORDER BY month;Quick Quiz
Tip
Tip
Practice Date Functions NOW DATE DATEDIFF in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Functions vary by database. Use EXTRACT() for portability. Always store dates in UTC.
Common Mistake
Warning
A common mistake with Date Functions NOW DATE DATEDIFF is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Date Functions NOW DATE DATEDIFF from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Date and time functions are critical for analytics, reporting, and scheduled operations.
- NOW() / CURRENT_TIMESTAMP - current date and time
- CURRENT_DATE - today's date only
- DATE(datetime) - extract date portion from datetime