Date & Time Functions

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.

Learn Date & Time Functions in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.

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.

Every query below runs against this little orders table. order_date is a DATE and order_ts is a 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".

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").

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

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.

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.

"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.

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.

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

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.

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

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary