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.
What You'll Learn
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".
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").
Your Turn: pull the month
Fill in the part and the alias to show each order's month number.
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.
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.
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.
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.
Reorder Challenge
These lines of a "monthly revenue" query are scrambled. Put them in the correct order:
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.
📘 Quick Reference
Syntax
Purpose
Frequently Asked Questions
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.
🎉 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
What does CURRENT_DATE return?
- Just the current time
- Both date and time
- Just today's date
- A random date
Answer: Just today's date. CURRENT_DATE gives only the date; CURRENT_TIMESTAMP (or NOW()) gives date and time together.
Within a single query, how often is CURRENT_TIMESTAMP evaluated?
- Once — every row gets the same value
- Once per row, so each row differs
- Never — it returns NULL
- Twice per query
Answer: Once — every row gets the same value. It is a snapshot of 'now' taken when the query runs, so every row sees the same value.
What does EXTRACT(MONTH FROM order_date) return?
- The full date as text
- The month name as text
- The day of the month
- The month as a number
Answer: The month as a number. EXTRACT pulls out a single component (here the month) and returns it as a number.
Which function formats a date into a friendly string in PostgreSQL?
- DATE_FORMAT
- TO_CHAR
- STR_TO_DATE
- FORMAT_DATE
Answer: TO_CHAR. Postgres uses TO_CHAR; MySQL uses DATE_FORMAT. The pattern letters differ.
Which is MySQL's function to format a date as a string?
- DATE_FORMAT
- TO_CHAR
- TEXT
- CONVERT_DATE
Answer: DATE_FORMAT. MySQL spells it DATE_FORMAT; TO_CHAR does not exist in MySQL.
In standard/Postgres SQL, what is DATE '2026-01-15' + INTERVAL '7 days'?
- 2026-01-08
- 2026-02-15
- 2026-01-22
- An error
Answer: 2026-01-22. INTERVAL adds a span of time, so 7 days after Jan 15 is Jan 22.
What does DATE_TRUNC('month', ts) do?
- Returns the month name
- Rounds a timestamp down to the start of its month
- Deletes the month component
- Adds one month
Answer: Rounds a timestamp down to the start of its month. It snaps the timestamp down to the first of the month — ideal for GROUP BY reporting.
Why can the same date code behave differently across MySQL and Postgres?
- Dates are stored differently in each row
- EXTRACT is not supported anywhere
- All date functions are fully standardized
- Functions like TO_CHAR and DATE_FORMAT are vendor-specific
Answer: Functions like TO_CHAR and DATE_FORMAT are vendor-specific. Date handling is poorly standardized; EXTRACT/INTERVAL are ANSI, but many format functions are vendor-specific.
Which data type stores both a calendar date and a clock time together?
- DATE
- TIMESTAMP
- TIME
- INTEGER
Answer: TIMESTAMP. DATE is date only, TIME is time only, TIMESTAMP stores both.
Can you compare dates with operators like < and >?
- No — dates cannot be compared
- Only with EXTRACT first
- Yes — dates are ordered, just quote the literal
- Only in MySQL
Answer: Yes — dates are ordered, just quote the literal. Dates and timestamps are ordered, so order_date < '2026-01-01' works like a number comparison.