Date and time functions — SQL & Relational Databases

Date and time manipulation is one of the most frequently required skills in data engineering. Nearly every analytical question has a temporal dimension: revenue this month versus last month, active users in the past 30 days, rolling 7-day average deliveries per match, or the time elapsed since a player's last century. SQL provides a rich set of date and time functions for extracting components, computing intervals, truncating to period boundaries, formatting for display, and converting between time zones. Mastering these functions is essential for writing correct temporal filters, period-over-period comparisons, and time-series aggregations.

Date and time errors are among the most insidious bugs in data pipelines because they often produce plausible-looking but subtly wrong results. A query that uses EXTRACT(MONTH FROM match_date) = 1 to find January matches will correctly identify January matches but cannot use an index on match_date, making it dramatically slower than a sargable range predicate. A pipeline that truncates timestamps to DATE before joining across time zones silently computes wrong totals when data spans midnight boundaries. Understanding both the correctness and performance implications of date functions separates engineers who write correct fast queries from those who write correct slow ones.

PostgreSQL's date and time type system distinguishes between DATE (calendar date only), TIME (time of day without date), TIMESTAMP (date and time without time zone awareness), and TIMESTAMPTZ (date and time with time zone stored as UTC). For data engineering pipelines that collect events from users across multiple time zones, TIMESTAMPTZ is the correct type for all event timestamps. Using TIMESTAMP without time zone for global event data is a category of bug that produces incorrect analytics whenever users span time zone boundaries — a mistake that is easy to make and difficult to detect after the fact.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.