Definition
- NOW() / CURRENT_TIMESTAMP – Return the current date and time;
CURRENT_DATEreturns just the date without the time portion. - DATE_TRUNC – Floors a timestamp to the start of a unit (
'month','year','day'); essential for computing sargable range bounds. - EXTRACT – Pulls a single field (year, month, day) from a timestamp, but wrapping the column in
EXTRACTmakes the predicate non-sargable. - Date arithmetic – Adding intervals: PostgreSQL
dt + INTERVAL '1 day', MySQLDATE_ADD(dt, INTERVAL 1 DAY), SQL ServerDATEADD(day, 1, dt). - Sargable date filter – Compare the bare column to computed range bounds using
>=and<, so the B-tree index can seek directly.
National Bank of India's monthly statement needs "all of Rajesh's October transactions." The slow way asks the database "for every transaction, pull out its month and check if it equals October" – it must touch every row. The fast way says "give me transactions from October 1st up to November 1st," which lets the index jump straight to that range.
Understanding the Concepts
What are NOW() and CURRENT_TIMESTAMP?
These functions return the current date and time. They are used for default values, comparisons, and computations.
-- PostgreSQL / MySQL
SELECT NOW(); -- 2026-10-25 14:30:45.123456
SELECT CURRENT_TIMESTAMP; -- 2026-10-25 14:30:45.123456
SELECT CURRENT_DATE; -- 2026-10-25 (date only)
-- National Bank of India: default created_at
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
What is DATE_TRUNC and why is it important?
DATE_TRUNC floors a timestamp to the start of a specified unit. It is the key function for building sargable date ranges.
-- PostgreSQL: DATE_TRUNC
SELECT DATE_TRUNC('month', '2026-10-25 14:30:45'::timestamp);
-- Result: 2026-10-01 00:00:00 (start of October)
SELECT DATE_TRUNC('year', '2026-10-25 14:30:45'::timestamp);
-- Result: 2026-01-01 00:00:00 (start of year)
SELECT DATE_TRUNC('day', '2026-10-25 14:30:45'::timestamp);
-- Result: 2026-10-25 00:00:00 (start of day)
-- Oracle: TRUNC
SELECT TRUNC(SYSDATE, 'MM') FROM dual;
-- Result: 2026-10-01 (start of month)
What is EXTRACT and why can it defeat the index?
EXTRACT pulls a single field from a timestamp. But when you use it in a WHERE clause, it wraps the indexed column in a function, making the predicate non-sargable.
-- EXTRACT pulls a field
SELECT EXTRACT(YEAR FROM '2026-10-25'::date); -- 2026
SELECT EXTRACT(MONTH FROM '2026-10-25'::date); -- 10
SELECT EXTRACT(DAY FROM '2026-10-25'::date); -- 25
-- NON-SARGABLE: EXTRACT on the indexed column
-- The B-tree is sorted by full timestamps, not extracted years
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE EXTRACT(MONTH FROM created_at) = 10
AND EXTRACT(YEAR FROM created_at) = 2026;
Seq Scan on transactions
Filter: (EXTRACT(month FROM created_at) = 10
AND EXTRACT(year FROM created_at) = 2026)
Rows Removed by Filter: 180,000,000
Execution Time: 42,000 ms -- 42 seconds!
How do you build a sargable date filter?
Use DATE_TRUNC to compute the bounds, then compare the bare column with a half-open range.
-- SARGABLE: half-open range on the bare column
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE created_at >= DATE_TRUNC('month', '2026-10-15'::date)
AND created_at < DATE_TRUNC('month', '2026-10-15'::date)
+ INTERVAL '1 month';
-- Equivalent to: WHERE created_at >= '2026-10-01'
-- AND created_at < '2026-11-01'
Index Scan using ix_txn_date on transactions
Index Cond: (created_at >= '2026-10-01'
AND created_at < '2026-11-01')
Execution Time: 95 ms -- 440x faster
More rewrite examples:
-- Filter by year: EXTRACT -> range
-- Wrong:
WHERE EXTRACT(YEAR FROM created_at) = 2026
-- Correct:
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01'
-- Filter by specific date: DATE() -> range
-- Wrong:
WHERE DATE(created_at) = '2026-10-25'
-- Correct:
WHERE created_at >= '2026-10-25'
AND created_at < '2026-10-26'
-- Filter by quarter:
-- Wrong:
WHERE EXTRACT(QUARTER FROM created_at) = 4
AND EXTRACT(YEAR FROM created_at) = 2026
-- Correct:
WHERE created_at >= '2026-10-01'
AND created_at < '2027-01-01'
What is date arithmetic and how does it differ across engines?
Date arithmetic adds or subtracts intervals from a timestamp. The syntax varies by engine.
-- PostgreSQL: dt + INTERVAL
SELECT CURRENT_DATE + INTERVAL '1 day'; -- tomorrow
SELECT CURRENT_DATE + INTERVAL '1 month'; -- next month
SELECT CURRENT_DATE - INTERVAL '30 days'; -- 30 days ago
-- MySQL: DATE_ADD / DATE_SUB
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY);
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);
-- SQL Server: DATEADD
SELECT DATEADD(day, 1, GETDATE());
SELECT DATEADD(month, -1, GETDATE());
-- National Bank of India: find transactions in the last 30 days
SELECT txn_id, amount, created_at
FROM transactions
WHERE account_id = 'SAV2001'
AND created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY created_at DESC;
What are DATEDIFF and formatting functions?
DATEDIFF computes the difference between two dates. Formatting functions convert timestamps to strings.
-- DATEDIFF: difference between dates
-- SQL Server / MySQL:
SELECT DATEDIFF(day, '2026-01-01', '2026-10-25'); -- 297 days
-- PostgreSQL: subtract dates directly
SELECT '2026-10-25'::date - '2026-01-01'::date; -- 297
-- Formatting: convert timestamp to string
-- PostgreSQL / Oracle:
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');
-- '2026-10-25 14:30:45'
-- MySQL:
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- '2026-10-25 14:30:45'
Why use a half-open upper bound?
A half-open upper bound (<) avoids double-counting boundary timestamps regardless of fractional-second precision.
-- Wrong: inclusive upper bound can miss or double-count
WHERE created_at >= '2026-10-01'
AND created_at <= '2026-10-31';
-- Misses timestamps on 2026-10-31 after midnight
-- e.g., '2026-10-31 23:59:59.999999' is included
-- but '2026-10-31 00:00:00.000001' through end of day varies
-- Correct: exclusive upper bound is always correct
WHERE created_at >= '2026-10-01'
AND created_at < '2026-11-01';
-- Includes every timestamp from Oct 1 00:00:00.000000
-- up to but not including Nov 1 00:00:00.000000
-- Works regardless of precision (microseconds, nanoseconds)
// com.bank.service.MonthlyReportService
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
import java.time.YearMonth;
import java.time.LocalDate;
public class MonthlyReportService {
/**
* Sargable monthly report using half-open date range.
* DATE_TRUNC logic done in Java to keep the SQL sargable.
*/
public BigDecimal getMonthlyTotal(
Connection conn, String accountId, YearMonth month)
throws SQLException {
LocalDate start = month.atDay(1); // Oct 1
LocalDate end = month.plusMonths(1).atDay(1); // Nov 1
String sql = """
SELECT COALESCE(SUM(amount), 0)
FROM transactions
WHERE account_id = ?
AND created_at >= ?
AND created_at < ?
""";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setDate(2, Date.valueOf(start)); // >= Oct 1
ps.setDate(3, Date.valueOf(end)); // < Nov 1
try (ResultSet rs = ps.executeQuery()) {
rs.next();
return rs.getBigDecimal(1);
}
}
}
}
What about time zones?
When the column is timestamptz, handle time zones explicitly. DATE_TRUNC on a timestamptz respects the session time zone.
-- Ensure consistent time zone handling
SET timezone = 'Asia/Kolkata';
SELECT DATE_TRUNC('month', NOW()::timestamptz);
-- Truncates in IST, then stores as UTC internally
-- For cross-timezone reports, use AT TIME ZONE
SELECT created_at AT TIME ZONE 'Asia/Kolkata'
FROM transactions
WHERE created_at >= '2026-10-01T00:00:00+05:30'
AND created_at < '2026-11-01T00:00:00+05:30';
What should a senior engineer know?
- Never wrap an indexed timestamp column in a function in the WHERE clause.
- Always use
DATE_TRUNC+ range or compute bounds in application code. - Use the half-open upper bound (
<) for correct boundary handling. - Handle
timestamptzexplicitly, especially in multi-timezone systems. - Verify the plan with
EXPLAIN ANALYZEafter every date-filter rewrite.
What are common interview follow-up questions?
- What is a sargable predicate and how does it relate to date filtering?
- How does DATE_TRUNC differ across PostgreSQL, Oracle, and MySQL?
- Why is a half-open range safer than an inclusive upper bound for timestamps?
- How do you handle time zones in date range queries?
Visual Overview
┌─────────────────────────────────────────────────┐
│ Sargable vs Non-Sargable Date Filtering │
├─────────────────────────────────────────────────┤
│ │
│ Non-Sargable (full scan): │
│ WHERE EXTRACT(MONTH FROM ts) = 10 │
│ AND EXTRACT(YEAR FROM ts) = 2026 │
│ ──► Must compute EXTRACT on every row │
│ ──► Seq Scan: 42 seconds on 200M rows │
│ │
│ Sargable (index scan): │
│ WHERE ts >= '2026-10-01' │
│ AND ts < '2026-11-01' │
│ ──► B-tree seeks to Oct 1, walks to Nov 1 │
│ ──► Index Scan: 95 ms │
│ │
│ Building bounds with DATE_TRUNC: │
│ lower = DATE_TRUNC('month', input_date) │
│ upper = lower + INTERVAL '1 month' │
│ WHERE ts >= lower AND ts < upper │
│ │
│ Half-open range (< not <=): │
│ >= Oct 1 00:00:00.000000 │
│ < Nov 1 00:00:00.000000 │
│ Correct for any precision (us, ns) │
└─────────────────────────────────────────────────┘
Key Takeaway
The common SQL date and time functions give you the current moment with NOW() or CURRENT_TIMESTAMP, truncation to a unit with DATE_TRUNC in PostgreSQL or TRUNC in Oracle, field extraction with EXTRACT, date arithmetic with interval addition or DATE_ADD and DATEADD, differences with DATEDIFF, and formatting with TO_CHAR or DATE_FORMAT. The critical performance idea is sargability: if you wrap the indexed timestamp column in a function, like filtering on the extracted month and year, the database must compute that function for every row and cannot use the index, which becomes a full scan.
The sargable fix is to compute a half-open range and compare the bare column to it. In the National Bank of India monthly statement, instead of filtering on the extracted month Rajesh computes the first of the month with DATE_TRUNC as the lower bound and adds one month for an exclusive upper bound, then filters where the timestamp is greater than or equal to the start and strictly less than the next month. That lets the index on account and timestamp serve the query as a range scan, and the exclusive upper bound guarantees no double-counting of boundary timestamps regardless of fractional-second precision.
Pankaj also insists on handling time zones explicitly when the column is timestamptz, using AT TIME ZONE for cross-timezone reports. After every rewrite he verifies with EXPLAIN ANALYZE that the plan now shows an Index Scan rather than a Sequential Scan, because a stray function or type mismatch can silently keep the scan.
Remember one sentence: never wrap the indexed date column in a function – filter with a half-open range so the index can be used.
---