Date & Time Functions

Reviewed & published by Brayan K

Almost every real database stores when something happened — when an order was placed, a user signed up, a payment cleared. SQL's date and time functions let you read those moments, do arithmetic on them, and reshape them into exactly the form a report needs.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll be able to grab the current moment, pull individual pieces out of a date, add and subtract intervals, measure the gap between two dates, and bucket rows by month for reporting.

What You'll Learn

• Get the current date, time, and timestamp
• Pull parts of a date out with EXTRACT
• Format dates for humans with TO_CHAR / DATE_FORMAT
• Add and subtract time using INTERVAL
• Measure the days between two dates
• Bucket rows by month with DATE_TRUNC

Our Sample Table: orders

Every query below runs against this little orders table. order_date is a DATE and order_ts is a TIMESTAMP.

1. What Time Is It? — CURRENT_DATE / CURRENT_TIMESTAMP

The simplest date functions take no arguments — they just report the current moment as the server sees it. They're evaluated once when the query runs, so every row in a query gets the same value.

CURRENT_TIMESTAMP is like glancing at a wall clock the instant the query starts. It doesn't tick row-by-row — it's a snapshot of "now".

-- The table this lesson uses (PostgreSQL):
CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    customer   TEXT,
    order_date DATE,           -- a calendar date, no clock time
    order_ts   TIMESTAMP,      -- the same moment, date AND time
    amount     NUMERIC(8,2)
);

INSERT INTO orders (order_id, customer, order_date, order_ts, amount) VALUES
    (101, 'Ava',  '2026-01-15'::date, '2026-01-15 09:12:00'::timestamp, 42.00),
    (102, 'Ben',  '2026-02-03'::date, '2026-02-03 17:40:00'::timestamp, 18.50),
    (103, 'Cleo', '2025-12-28'::date, '2025-12-28 11:05:00'::timestamp, 96.25);

-- The "what time is it right now?" functions
SELECT
    CURRENT_DATE      AS today,        -- just the date
    CURRENT_TIME      AS clock,        -- just the time
    CURRENT_TIMESTAMP AS right_now;    -- date AND time together

-- Result (your values will differ — these are evaluated at run time):
-- today      | clock    | right_now
-- 2026-06-17 | 14:08:31 | 2026-06-17 14:08:31
--
-- NOW() is a common alias for CURRENT_TIMESTAMP in MySQL/Postgres.

2. EXTRACT — Pull a Piece Out of a Date

EXTRACT(part FROM date) returns a single component — the year, month, day, hour, quarter, or day-of-week. It's the go-to for grouping ("orders per year") and filtering ("anything from December").

-- Uses the orders table from the first example.
-- EXTRACT pulls one piece out of a date/timestamp
SELECT
    order_id,
    order_date,
    EXTRACT(YEAR  FROM order_date) AS yr,
    EXTRACT(MONTH FROM order_date) AS mon,
    EXTRACT(DAY   FROM order_date) AS dy
FROM orders;

-- You can also EXTRACT(HOUR / MINUTE / DOW / QUARTER FROM ...).

-- ✅ Expected result:
-- order_id | order_date | yr | mon | dy
-- 101 | 2026-01-15 | 2026 | 1 | 15
-- 102 | 2026-02-03 | 2026 | 2 | 3
-- 103 | 2025-12-28 | 2025 | 12 | 28

Your Turn: pull the month

Fill in the part and the alias to show each order's month number.

-- Uses the orders table from the first example.
-- 🎯 YOUR TURN — fill in the blanks
-- Goal: show each order's id and the MONTH it was placed.

SELECT
    order_id,
    EXTRACT(____ FROM order_date) AS ____   -- 👉 part = MONTH, alias = month_placed
FROM orders;

-- ✅ Expected: 3 rows
-- order_id | month_placed
-- 101      | 1
-- 102      | 2
-- 103      | 12

3. Formatting Dates for Humans

Stored dates look like 2026-01-15. To show Jan 15, 2026 on a dashboard, format them. Postgres uses TO_CHAR; MySQL uses DATE_FORMAT. The pattern letters differ, so this is one place to keep the docs handy.

-- Uses the orders table from the first example.
-- Turning a date into a human-friendly string.
-- Postgres uses TO_CHAR; MySQL uses DATE_FORMAT.

-- Postgres:
SELECT TO_CHAR(order_date, 'Mon DD, YYYY') AS pretty
FROM orders;

-- MySQL equivalent:
-- (left as a comment so this page still runs: Postgres has no DATE_FORMAT,
--  and asking it for one is an error, not a different answer)
-- SELECT DATE_FORMAT(order_date, '%b %d, %Y') AS pretty
-- FROM orders;

-- Result (both):
-- pretty
-- Jan 15, 2026
-- Feb 03, 2026
-- Dec 28, 2025

-- ✅ Expected output:
-- pretty
-- Jan 15, 2026
-- Feb 03, 2026
-- Dec 28, 2025

-- ✅ Expected result:
-- pretty
-- Jan 15, 2026
-- Feb 03, 2026
-- Dec 28, 2025

4. Date Math with INTERVAL

You can add and subtract spans of time. The ANSI form is date + INTERVAL '7 days'. MySQL spells it DATE_ADD(date, INTERVAL 7 DAY), and SQLite uses date(d, '+7 days') — same idea, different wrapper.

-- Uses the orders table from the first example.
-- Adding and subtracting time.
-- Standard SQL uses INTERVAL; the literal differs slightly by engine.

-- Postgres / standard:
SELECT
    order_date,
    order_date + INTERVAL '7 days'  AS ships_by,
    order_date - INTERVAL '1 month' AS one_month_ago
FROM orders;

-- MySQL: DATE_ADD(order_date, INTERVAL 7 DAY)
-- SQLite: date(order_date, '+7 days')

-- Note: DATE + INTERVAL gives back a TIMESTAMP in Postgres, which is why
-- the two computed columns carry a 00:00:00 clock time and order_date does not.

-- ✅ Expected result:
-- order_date | ships_by | one_month_ago
-- 2026-01-15 | 2026-01-22 00:00:00 | 2025-12-15 00:00:00
-- 2026-02-03 | 2026-02-10 00:00:00 | 2026-01-03 00:00:00
-- 2025-12-28 | 2026-01-04 00:00:00 | 2025-11-28 00:00:00

5. The Gap Between Two Dates

"How many days ago was this order?" In Postgres you subtract two dates and get an integer number of days. MySQL has DATEDIFF(a, b); SQLite uses julianday(). This powers age, tenure, and overdue calculations everywhere.

-- Uses the orders table from the first example.
-- How many days between two dates?

-- Postgres: subtract two dates -> integer days
SELECT order_id, CURRENT_DATE - order_date AS days_since
FROM orders;

-- MySQL:  DATEDIFF(CURRENT_DATE, order_date)
-- SQLite: julianday('now') - julianday(order_date)

-- Result (assuming today is 2026-06-17):
-- order_id | days_since
-- 101      | 153
-- 102      | 134
-- 103      | 171

-- CURRENT_DATE moves every day, so that answer is only true on one day.
-- Pin the date and the same subtraction becomes reproducible:
SELECT order_id, DATE '2026-06-17' - order_date AS days_since
FROM orders;

6. DATE_TRUNC — Bucket by Period

DATE_TRUNC('month', ts) rounds a timestamp down to the start of the month (or day, week, year…). Pair it with GROUP BY and you get clean monthly or yearly summaries — the backbone of time-series reporting.

-- Uses the orders table from the first example.
-- DATE_TRUNC snaps a timestamp down to a unit — perfect for grouping.
SELECT
    DATE_TRUNC('month', order_ts) AS month_bucket,
    COUNT(*)                      AS orders
FROM orders
GROUP BY DATE_TRUNC('month', order_ts)
ORDER BY month_bucket;

-- ✅ Expected result:
-- month_bucket | orders
-- 2025-12-01 00:00:00 | 1
-- 2026-01-01 00:00:00 | 1
-- 2026-02-01 00:00:00 | 1

Reorder Challenge

These lines of a "monthly revenue" query are scrambled. Put them in the correct order:

A.   GROUP BY DATE_TRUNC('month', order_ts)
B.   SELECT DATE_TRUNC('month', order_ts) AS mth, SUM(amount) AS revenue
C.   ORDER BY mth
D.   FROM orders

Quick Recall

1) What does EXTRACT(YEAR FROM '2025-12-28') return?

2025 — EXTRACT returns a number, not a string. It pulls the requested component out of the date.

2) In Postgres, what is DATE '2026-01-15' + INTERVAL '7 days'?

2026-01-22. INTERVAL adds a span of time to a date or timestamp.

3) You want one row per month with a count. Which function buckets the timestamps?

DATE_TRUNC('month', ts) in the SELECT and GROUP BY. It snaps every timestamp down to the first of its month so they group together.

Common Errors (and the fix)

• Using MySQL syntax in Postgres: DATE_FORMAT doesn't exist in Postgres (use TO_CHAR), and TO_CHAR doesn't exist in MySQL. Match the function to your engine.
• Comparing a date to an unquoted string: write order_date > '2026-01-01' with quotes so the engine parses it as a date.
• Off-by-one with months: adding INTERVAL '1 month' to Jan 31 may land on Feb 28/29, not Feb 31. The engine clamps to the last valid day.
• Forgetting GROUP BY: using DATE_TRUNC with COUNT(*) but not grouping by the same expression errors or returns one row.
• Time-zone surprises: CURRENT_TIMESTAMP reflects the server's zone. Store and compute in UTC to stay sane.

Mini-Challenge: Order Due Dates

Combine EXTRACT, date math, and aliasing. Write it, then run it in a playground to confirm against the expected result in the comments.

-- Uses the orders table from the first example.
-- 🎯 MINI-CHALLENGE
-- Using EXTRACT, date math, and aliasing:
--   1. Show order_id and order_date
--   2. Add a column "due_date" that is order_date + 30 days
--   3. Add a column "order_year" using EXTRACT
--   4. Only return columns — no WHERE needed
--
-- ✅ Expected: 3 rows with order_id, order_date, due_date, order_year
--    e.g. 101 | 2026-01-15 | 2026-02-14 | 2026

-- your query here

🎉 Lesson Complete

• ✅ CURRENT_DATE / CURRENT_TIMESTAMP give you "now"
• ✅ EXTRACT pulls year/month/day/hour out of a date
• ✅ TO_CHAR / DATE_FORMAT render dates for humans
• ✅ INTERVAL adds and subtracts spans of time
• ✅ DATE_TRUNC buckets rows by period for reports
• ✅ Next: CAST & CONVERT — changing a value's data type

Practice quiz

Continue this course

• Previous: String Functions
• Next: CAST & CONVERT — Convert between data types in your queries
• Quick reference: SQL cheat sheet › Date & Time

Frequently asked questions

Why does the same date function behave differently in MySQL vs Postgres?

SQL date handling is one of the least standardized areas. The ANSI standard defines EXTRACT and INTERVAL, but functions like TO_CHAR, DATE_FORMAT, DATEDIFF, and DATE_ADD are vendor-specific. Always check your engine's docs, and prefer the standard EXTRACT/INTERVAL syntax when you want portable code.

What's the difference between DATE, TIME, and TIMESTAMP?

DATE stores only a calendar date (2026-06-17). TIME stores only a clock time (14:08:31). TIMESTAMP stores both together. Use TIMESTAMP when you need to know the exact moment something happened, and DATE when the time of day is irrelevant.

How do time zones affect date functions?

CURRENT_TIMESTAMP usually returns the server's time zone. Postgres offers TIMESTAMPTZ which stores values in UTC and converts on display. If your app spans regions, store timestamps in UTC and convert only at the presentation layer to avoid off-by-an-hour bugs.

Can I compare dates with < and >?

Yes. Dates and timestamps are ordered, so order_date < '2026-01-01' works exactly like a number comparison. Always quote date literals as strings; the engine parses them into real date values.