Vivek Kumar

Posted on May 4

SQL Date & Time Functions: A Practical Guide for Real-World Queries

#postgres #mysql #sql #database

Working with dates and times is one of those things that sounds simple until you're staring at a timestamp column wondering why your "last 30 days" query returns unexpected results. Date and time functions are essential for analytics, reporting, and any application that tracks events over time — yet the subtle differences between database engines trip up even experienced developers.

In this guide, we'll cover the most useful SQL date/time functions across PostgreSQL and MySQL — two of the most widely used databases — with practical, real-world examples. By the end, you'll know how to confidently filter by time ranges, calculate durations, group data by period, and avoid the most common pitfalls.

Why Date/Time Functions Matter

Think about how many queries involve time: "Show me orders placed in the last 7 days," "Calculate how long customers have been subscribed," "Group revenue by month." Nearly every meaningful business question has a temporal dimension.

The challenge is that SQL databases don't all speak the same date dialect. PostgreSQL has DATE_TRUNC and AGE. MySQL has DATE_FORMAT and TIMESTAMPDIFF. Standard SQL gives you EXTRACT and CURRENT_TIMESTAMP. Knowing what's available — and what differs — saves you hours of debugging.

We'll use a consistent example throughout: an e-commerce database with orders and customers tables.

-- Our example schema
CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  name          VARCHAR(100),
  joined_at     TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  order_id      INT PRIMARY KEY,
  customer_id   INT REFERENCES customers(customer_id),
  placed_at     TIMESTAMP NOT NULL,
  shipped_at    TIMESTAMP,
  total_amount  DECIMAL(10,2)
);

1. Getting the Current Date and Time

The most basic need: "what time is it right now?" Every major database supports these, but syntax varies slightly:

-- PostgreSQL
SELECT NOW();                  -- current timestamp with time zone
SELECT CURRENT_TIMESTAMP;      -- same as NOW(), SQL standard
SELECT CURRENT_DATE;           -- date only (no time)
SELECT CURRENT_TIME;           -- time only

-- MySQL
SELECT NOW();                  -- current datetime
SELECT CURDATE();              -- date only
SELECT CURTIME();              -- time only
SELECT UTC_TIMESTAMP();        -- current UTC datetime (very useful!)

Real-world use: Find all orders placed today.

-- PostgreSQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at::date = CURRENT_DATE;

-- MySQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE DATE(placed_at) = CURDATE();

Note: Casting a timestamp to date (or using DATE()) forces the database to discard the time component before comparing — otherwise placed_at = CURRENT_DATE would fail because a timestamp like 2026-04-28 14:33:00 is not equal to the date 2026-04-28.

2. Extracting Parts of a Date

Sometimes you need just the year, month, or hour from a timestamp. EXTRACT is the SQL-standard way to do this, supported by both PostgreSQL and MySQL:

-- Standard SQL — works in both PostgreSQL and MySQL
SELECT
  order_id,
  placed_at,
  EXTRACT(YEAR  FROM placed_at) AS order_year,
  EXTRACT(MONTH FROM placed_at) AS order_month,
  EXTRACT(DOW   FROM placed_at) AS day_of_week,  -- 0=Sunday in PostgreSQL
  EXTRACT(HOUR  FROM placed_at) AS order_hour
FROM orders;

Real-world use: Group orders by month to see monthly revenue trends.

SELECT
  EXTRACT(YEAR  FROM placed_at) AS yr,
  EXTRACT(MONTH FROM placed_at) AS mo,
  COUNT(*)                      AS order_count,
  SUM(total_amount)             AS revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

MySQL shorthand functions: MySQL also offers dedicated extraction functions that are a bit more readable:

-- MySQL-specific shortcuts
SELECT
  YEAR(placed_at)    AS order_year,
  MONTH(placed_at)   AS order_month,
  DAY(placed_at)     AS order_day,
  HOUR(placed_at)    AS order_hour,
  DAYNAME(placed_at) AS weekday_name   -- e.g. "Tuesday"
FROM orders;

3. Date Arithmetic with INTERVAL

Adding or subtracting time from a date is a common need: "all orders from the last 30 days," "subscriptions expiring in the next 7 days," etc.

-- PostgreSQL: use INTERVAL keyword
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL '30 days';

-- MySQL: use DATE_SUB() or INTERVAL notation
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL 30 DAY;

-- Also valid in MySQL:
SELECT * FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

INTERVAL supports a wide range of units:

-- PostgreSQL examples
NOW() + INTERVAL '1 year'
NOW() - INTERVAL '3 months'
NOW() + INTERVAL '2 hours 30 minutes'
'2026-01-01'::date + INTERVAL '90 days'

-- MySQL examples
DATE_ADD(NOW(), INTERVAL 1 YEAR)
DATE_ADD(NOW(), INTERVAL -3 MONTH)
DATE_ADD(shipped_at, INTERVAL 14 DAY)   -- estimated delivery date

Real-world use: Flag orders that shipped more than 14 days ago but never had a follow-up.

-- PostgreSQL
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL
  AND shipped_at < NOW() - INTERVAL '14 days';

4. Calculating Differences Between Dates

How many days between two dates? How long has a customer been with us? These questions require date difference functions.

DATEDIFF (MySQL)

-- MySQL: DATEDIFF returns difference in days
SELECT
  order_id,
  placed_at,
  shipped_at,
  DATEDIFF(shipped_at, placed_at) AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL;

TIMESTAMPDIFF (MySQL)

TIMESTAMPDIFF is more flexible — you choose the unit:

-- MySQL: specify the unit you want
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  TIMESTAMPDIFF(YEAR,  c.joined_at, NOW()) AS years_as_customer,
  TIMESTAMPDIFF(MONTH, c.joined_at, NOW()) AS months_as_customer,
  TIMESTAMPDIFF(DAY,   c.joined_at, NOW()) AS days_as_customer
FROM customers c;

AGE (PostgreSQL)

PostgreSQL has the elegant AGE() function that returns a human-readable interval:

-- PostgreSQL
SELECT
  customer_id,
  name,
  joined_at,
  AGE(NOW(), joined_at)          AS customer_age,  -- e.g. "2 years 3 mons 12 days"
  EXTRACT(DAY FROM AGE(NOW(), joined_at))           AS days_as_customer
FROM customers;

Real-world use: Find customers who joined more than 1 year ago but have placed no order in the last 90 days (candidates for a win-back campaign).

-- PostgreSQL
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  MAX(o.placed_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.joined_at < NOW() - INTERVAL '1 year'
GROUP BY c.customer_id, c.name, c.joined_at
HAVING MAX(o.placed_at) < NOW() - INTERVAL '90 days'
    OR MAX(o.placed_at) IS NULL;

5. Truncating Dates with DATE_TRUNC (PostgreSQL)

DATE_TRUNC is a PostgreSQL superpower for grouping data by time periods. It rounds a timestamp down to the start of the given period:

SELECT DATE_TRUNC('month', NOW());
-- Result: 2026-04-01 00:00:00+00

SELECT DATE_TRUNC('week',  NOW());
-- Result: 2026-04-27 00:00:00+00 (start of current week, Monday)

SELECT DATE_TRUNC('year',  NOW());
-- Result: 2026-01-01 00:00:00+00

Supported precision values include: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century.

Real-world use: Weekly revenue report with clean week boundaries:

-- PostgreSQL
SELECT
  DATE_TRUNC('week', placed_at)  AS week_start,
  COUNT(*)                        AS orders,
  SUM(total_amount)               AS revenue,
  AVG(total_amount)               AS avg_order_value
FROM orders
WHERE placed_at >= NOW() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', placed_at)
ORDER BY week_start;

In MySQL, you can approximate this with DATE_FORMAT:

-- MySQL: group by week using DATE_FORMAT
SELECT
  DATE_FORMAT(placed_at, '%Y-%u')  AS year_week,  -- e.g. "2026-17"
  COUNT(*)                          AS orders,
  SUM(total_amount)                 AS revenue
FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 12 WEEK)
GROUP BY DATE_FORMAT(placed_at, '%Y-%u')
ORDER BY year_week;

6. Formatting Dates for Display

Raw timestamps aren't user-friendly. Format them for reports and APIs:

-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(placed_at, 'Month DD, YYYY')   AS display_date;  -- "April 28, 2026"
SELECT TO_CHAR(placed_at, 'YYYY-MM-DD HH24:MI') AS display_dt;   -- "2026-04-28 14:33"
SELECT TO_CHAR(placed_at, 'Day')               AS weekday;       -- "Tuesday"

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(placed_at, '%M %d, %Y')     AS display_date;  -- "April 28, 2026"
SELECT DATE_FORMAT(placed_at, '%Y-%m-%d %H:%i') AS display_dt;   -- "2026-04-28 14:33"
SELECT DATE_FORMAT(placed_at, '%W')             AS weekday;       -- "Tuesday"

Common Mistakes and Gotchas

1. Applying a function to an indexed column kills performance

If you have an index on placed_at, this query can't use it:

-- ❌ Wrapping the column in a function prevents index usage
WHERE DATE(placed_at) = CURDATE()          -- MySQL
WHERE DATE_TRUNC('day', placed_at) = ...   -- PostgreSQL

Instead, use a range comparison that leaves the column untouched:

-- ✅ Range filter — the index on placed_at can be used
WHERE placed_at >= '2026-04-28 00:00:00'
  AND placed_at <  '2026-04-29 00:00:00'

-- Or dynamically:
WHERE placed_at >= CURRENT_DATE
  AND placed_at <  CURRENT_DATE + INTERVAL '1 day'

2. Ignoring time zones

NOW() in PostgreSQL returns timestamptz (with time zone). If your app stores timestamps in UTC but your NOW() uses a local session timezone, you can get wildly wrong results. Always be explicit:

-- PostgreSQL: force UTC
WHERE placed_at >= NOW() AT TIME ZONE 'UTC' - INTERVAL '24 hours'

In MySQL, use UTC_TIMESTAMP() instead of NOW() when you want UTC regardless of session settings.

3. NULL shipped_at breaks duration calculations

If shipped_at is NULL (order not yet shipped), a DATEDIFF or subtraction involving it returns NULL — not an error, just silent NULL propagation. Always handle this:

-- MySQL
SELECT
  order_id,
  COALESCE(DATEDIFF(shipped_at, placed_at), 'Not yet shipped') AS fulfillment_days
FROM orders;

4. DATEDIFF argument order differs by database

MySQL: DATEDIFF(end_date, start_date) → positive if end is after start
SQL Server: DATEDIFF(unit, start_date, end_date) → note the unit comes first!
PostgreSQL: use subtraction directly: end_date - start_date

This trips up developers switching between databases constantly.

Summary: Key Takeaways

Working with dates in SQL is a skill that pays dividends across virtually every query you'll write for analytics or reporting. The core ideas to remember:

Use NOW() / CURRENT_TIMESTAMP for the current moment; CURRENT_DATE for today's date
EXTRACT() pulls out specific date parts (year, month, day of week) — it's standard SQL
Use INTERVAL for date arithmetic — keep the indexed column bare on the left side of your WHERE clause
DATEDIFF / TIMESTAMPDIFF (MySQL) and AGE() / simple subtraction (PostgreSQL) handle duration calculations
DATE_TRUNC (PostgreSQL) is your best friend for grouping by week/month/quarter
DATE_FORMAT (MySQL) and TO_CHAR (PostgreSQL) format dates for display
Always consider NULLs and time zones — they're the source of most date-related bugs

What's Your Favorite Date Function?

Date functions are deceptively powerful. Once you're comfortable with DATE_TRUNC for time-series aggregation or TIMESTAMPDIFF for cohort analysis, your SQL queries go from useful to genuinely insightful.

Drop a comment below — what's the most useful date/time query pattern you use regularly? I'd love to see what real-world problems you're solving! And if you found this helpful, share it with a colleague who's wrestling with a "last 30 days" filter. 🗓️

DEV Community