SQL Date Functions: DATE_TRUNC, DATEDIFF

You'll reach for DATE_TRUNC more times in your first month on the job than any other date function. Cohort retention, weekly active users, monthly revenue rollups: all of them are one DATE_TRUNC call away. This page is the reference you'll keep open in a second tab. It covers Postgres, Snowflake, BigQuery, and MySQL syntax side by side so you don't have to hunt for the right flavor.

SQL Date Functions FAQ

What are the most important SQL date functions for data engineers?+

DATE_TRUNC and EXTRACT are the two most-used date functions in data engineering. DATE_TRUNC rounds a timestamp down to a specified precision (day, week, month, quarter, year) and is essential for time-series grouping. EXTRACT pulls a specific component (year, month, day, hour) from a timestamp. After those, DATEDIFF (or date subtraction), CURRENT_DATE/CURRENT_TIMESTAMP, and INTERVAL are the next most common. These five functions cover 95% of date manipulation needs in analytical SQL.

How do date functions differ across SQL databases?+

The core concepts are the same but syntax varies. DATE_TRUNC works in PostgreSQL, Snowflake, BigQuery, and Redshift with the same syntax. But MySQL does not have DATE_TRUNC (you use DATE_FORMAT or date arithmetic instead). DATEDIFF takes different argument orders: MySQL and SQL Server use DATEDIFF(date1, date2) or DATEDIFF(unit, date1, date2), while PostgreSQL uses simple subtraction (date1 - date2). EXTRACT is mostly consistent but the field names vary (DAYOFWEEK vs DOW vs ISODOW). Always check your specific database documentation.

How should I handle timezones in SQL?+

Store timestamps in UTC. Always. Then convert to local time at query time using AT TIME ZONE (PostgreSQL, Snowflake) or CONVERT_TIMEZONE (Snowflake) or TIMESTAMP(timestamp, timezone) (BigQuery). This avoids ambiguity around daylight saving time transitions. When grouping by day, specify whose day you mean: DATE_TRUNC('day', created_at AT TIME ZONE 'America/New_York') groups by US Eastern days, not UTC days. In interviews, if you mention timezone handling unprompted, it signals real-world pipeline experience.

02 / Why practice

Time-Series Queries, One At A Time

01
Active recall beats re-reading by 50%
Cognitive-science meta-reviews (Dunlosky et al., 2013) rank practice testing as a top-tier study technique, while re-reading and highlighting rank near the bottom
02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
03
Five problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition

Start Practicing

Related Guides

Date Functions Practice→

Hands-on date manipulation problems with real SQL execution

SQL Interview Questions→

Complete guide to every SQL topic tested in data engineering interviews

SQL Tutorial→

Learn SQL from scratch with interactive examples and progressive difficulty