Date Functions

Think of date functions as the calendar tools in your SQL toolbox: they help you ask when, not just what. Interviewers love this because tiny time mistakes cause big bugs in reports, billing, and alerts.

Question: What are date functions in SQL, and how do you use them for filtering and reporting?

Answer: Date functions let you get the current date or timestamp, pull out parts like year or month, and do calendar math with intervals. They are also used to group rows into days or months, or to find records from today, this week, or last month. The key idea is to keep time handling consistent, especially around time zones.

Interview-Ready Answer: I use SQL date functions to read the current date or time, extract parts like year and month, and shift values with intervals. For filtering, I usually prefer a range such as created_at >= CURRENT_DATE and created_at < CURRENT_DATE + INTERVAL '1 day' because it is clearer and often index-friendly. In PostgreSQL, I also watch out for time zones, since a timestamp can fall on a different calendar day depending on how it is interpreted.

What date functions really do

Date functions turn a stored date or timestamp into something useful for logic: a day number, a month bucket, a shifted deadline, or the current moment. The exact names vary by database, but the ideas are the same across SQL engines.

1. The parser reads a date literal like DATE '2024-02-29' or a timestamp literal and converts it into the database's internal format.
2. A function such as CURRENT_DATE, EXTRACT, or date_trunc runs. Some values are fixed for the statement or transaction, while others are computed for every row.
3. EXTRACT gives you a number, date_trunc snaps a timestamp down to a boundary like the start of the day, and interval arithmetic adds or subtracts calendar time.
4. If the expression appears in WHERE, the optimizer checks whether it can use an index. A predicate that wraps the column is often non-sargable (meaning the database cannot easily seek with an index).
5. If the value is a timestamptz or similar type, the session time zone can change which calendar day you see.

Common functions and what they return

Under the hood mindset: a date function is usually cheap by itself, about O(1) for one value. The expensive part is usually the scan. On a 50 million row table, created_at >= ... AND created_at < ... can let a B-tree index jump straight to the matching slice, while created_at::date = ... may force the engine to examine far more rows. That is why interviewers care about how you write the filter, not just whether it returns the right answer.

Range filter vs casting the column

Practical rule: use functions to transform values for display or grouping, but use raw-column range predicates for filtering whenever possible. If you truly need the exact same expression often, consider an expression index such as one on (created_at::date) in PostgreSQL.

Time zones and month-end edge cases

Two common traps are midnight and month boundaries. A timestamp that is tomorrow in UTC might still be today in New York, and adding one month to January 31 lands at the end of February, not on a neat 31st that does not exist. Those are normal calendar rules, not bugs in SQL.

Version and dialect note: this example uses PostgreSQL-style functions like date_trunc and INTERVAL. Other databases use different names, but the same ideas apply: current time, extract parts, bucket, and do calendar math.

Real-World Example: Imagine a checkout service that produces a daily revenue report. The team wants all payments made on a customer's local day, but the table stores timestamps in UTC. One developer writes WHERE paid_at::date = CURRENT_DATE and assumes that means 'today' everywhere. Around midnight, orders start landing in the wrong report because app servers, analysts, and customers are not all using the same time zone.

What goes wrong shows up fast: the dashboard is off by one day, finance sees revenue gaps, and support tickets mention missing receipts near midnight. Logs often reveal the pattern: rows around 23:30 UTC or 00:15 UTC appear in the wrong bucket. The fix is to choose one explicit time zone for reporting and use a half-open range on the raw timestamp, so the query is exact and index-friendly.

Follow-up & Tricky Questions:

• What is the difference between DATE, TIMESTAMP, and TIMESTAMPTZ? DATE stores only a calendar day, TIMESTAMP stores date plus time, and TIMESTAMPTZ stores a moment in time with time zone handling. For reporting across regions, TIMESTAMPTZ is usually safer.
• How do you get the first day of a month? In PostgreSQL, date_trunc('month', some_ts) gives the first instant of that month. If you only need a date, cast or convert the result appropriately.
• How do you get the last day of a month? A common pattern is date_trunc('month', some_date) + INTERVAL '1 month' - INTERVAL '1 day'. This works because you jump to the next month and step back one day.
• Why is a range filter better than column::date = ...? The range filter usually keeps the column raw, which lets an index on the timestamp help. The cast version is readable, but it may slow down large tables and can change meaning with time zones.
• What is the difference between CURRENT_TIMESTAMP and CLOCK_TIMESTAMP() in PostgreSQL? CURRENT_TIMESTAMP is fixed at the start of the transaction, while clock_timestamp() returns the actual wall-clock time at the moment it is called. That matters inside long transactions or slow queries.
• Does CURRENT_DATE change while a query is running? No, in PostgreSQL it is stable for the transaction, so one statement sees one consistent date. That stability is useful for repeatable reports.
• Is EXTRACT(MONTH FROM ts) a date? No, it returns a number, often a floating-point value in PostgreSQL. Use it for comparisons or grouping, not for storing a calendar date.
• Is BETWEEN safe for timestamp ranges? Only if you really want both endpoints included. For day filters, a half-open range like >= start and < next_start is safer because it avoids double-counting midnight.

Tricky / gotcha questions:

• Does created_at::date = CURRENT_DATE always mean the same day for all users? No. With timestamptz, the cast depends on the session time zone, so two users can see different dates for the same moment.
• Is date_trunc('day', created_at) good in a WHERE clause? It is fine for readability, but it often blocks index use on the original column. Use it for grouping, and use a range predicate for filtering.
• Do months always have 30 days when you add intervals? No. Calendar intervals respect real month lengths, so February and leap years behave differently from fixed-day math.

Common Mistakes:

• Wrapping the column in a function inside WHERE. Fix: prefer a raw-column range predicate so the index can be used.
• Ignoring time zones. Fix: pick one reporting zone, usually UTC, and convert explicitly when needed.
• Using BETWEEN for timestamp days. Fix: use a half-open range to avoid double-counting the end boundary.
• Confusing display formatting with date logic. Fix: use functions like EXTRACT and date_trunc for logic, and TO_CHAR only for presentation.

Memory Hook: Picture a train station clock: first choose the station time zone, then read the exact minute, then group trains into morning or afternoon bins. If you mix those steps up, you send the right train to the wrong platform.

Cheat Sheet:

• CURRENT_DATE = today, no time part.
• CURRENT_TIMESTAMP = current moment.
• EXTRACT = pull out year, month, day, hour.
• date_trunc = snap to day/month boundary.
• For filters, prefer >= start and < next_start.
• Watch time zones and month-end behavior.

Practice Tasks:

• Write a query that returns all rows from the last 7 days.
• Group an orders table by month and count the rows per month.
• Find all timestamps that fall on leap day in UTC, even if the session time zone is different.