✨ Try our new interactive demo
Date Trunc is a tool in SQL to truncate a date or timestamp by a specified interval (such as ‘day’, ‘week’, or ‘month’) in PostgreSQL. It is a very common expression used in SQL for powerful aggregations. This piece dives into how it works and how you can use Date Trunc for your SQL and insights.
To understand Date Trunc, first we need to understand the anatomy of the date objects we are truncating. Typically, we will be looking at a time object that looks like the following: 2024-01-02 11:30:20.123Z
To break this down, the first number is the year, followed by month, then day. We then have a space, followed by the hour, minute, and second. The numbers after the period represent the milliseconds. The ‘Z’ is the most complex part, but is only the UTC time, which is a timezone that goes through the UK, Greenland, Iceland, Portugal, and parts of West Africa. It is a common timezone for which everything is standardized to avoid confusions. To put it all together, this date would represent January 1, 2024 at 11:30am, 20.123 seconds into the minute in the UK.
Here is the syntax for date trunc: Date_Trunc(interval, column)
While it does vary by engine, below are the most typical offerings for date_trunc intervals:
Year: Truncates to the first day of the year.
Quarter: Truncates to the first day of the quarter.
Month: Truncates to the first day of the month.
Week: Truncates to the first day of the week (note that the definition of the first day of the week can vary).
Day: Truncates to the beginning of the day (midnight).
Hour: Truncates to the start of the hour.
Minute: Truncates to the start of the minute.Second: Truncates to the start of the second.
The column chosen must be a date object of some kind, typically a datetime or a timestamp field.
Suppose we have data in the following format:
CreatedDate | Sale Price
Jan 1, 2024 | $50
Jan 7, 2024 | $10
Jan 11, 2024 | $20
Jan 21, 2024 | $30
Feb 4, 2024 | $50
Feb 12, 2024 | $40
Suppose we wanted to know our total sales by month. How would we go about doing that? This is where date trunc comes in handy. By just writing:
Select date_trunc(‘month’, CreatedDate), Sum(SalePrice) From Table
We are able to the the results:
January: $100
February: $90
Which represents the total sales by month. It’s really that easy! Date Trunc is a powerful tool for enabling insights over time intervals, which is integral to business insights.
The above results have been formatted to be more human readabale. In reality, the results returned from SQL are more like:
2024-01-01 00:00:00.000Z, 100
2024-02-01 00:00:00.000Z, 90
As you can see, the truncation happens on the UTC date objects. The values after the month are set to defaults (0s or 1s depending on the field), which results in all timestamps in the same month to be evaluated as the same timestamp, thusly aggregating the data.
So the truncation becomes:
CreatedDate | SalePrice
2024-01-01 00:00:00.000Z | $50
2024-01-01 00:00:00.000Z | $10
2024-01-01 00:00:00.000Z | $20
2024-01-01 00:00:00.000Z | $30
2024-02-01 00:00:00.000Z | $50
2024-02-01 00:00:00.000Z | $40
The SQL engine can then easily match the matching values to do the proposed aggregation.
This can then be displayed into something more human readable as desired, which many modern BI tools are able to automatically do for ease of use.
Postgres and derivative offerings utilize Date_Trunc for date truncation needs. However, other SQL engines perform this same operation in a slightly different way. Below are some examples of how other engines aggregate date by month.
STR_TO_DATE(DATE_FORMAT(current_date, '%Y-%m-01'), '%Y-%m-%d')
TRUNC(current_date, 'MM')
DATEADD(month, DATEDIFF(month, 0, current_date), 0)
You can try out date functions yourself here.
Date Trunc is an incredibly useful tool when it comes to SQL charting. In general, Date Trunc is used every single time that a date aggregation needs to be done. Let’s take a look at Explo’s implementation of Date Trunc under-the-hood.
Below is a quick example of a company like Airbnb setting up a chart of their total reviews by month.
On the left hand side is an intuitive line chart setup tool, where a user can setup the X Axis as the month of the reviews and the Y Axis is the Count of Id. This is then automagically translated on the right hand side into the SQL you see, which truncates the date by month and counts the number of unique entries per month. Wow, easy! Additionally, you can dynamically change the date grouping to Weekly, Monthly, or Yearly. Once the data is calculated, it is then displayed in the line chart.
A lot of modern embedded analytics offers this simple point-and-click interface for taking date fields, putting them into an Axis, then having that translated into SQL for a charting library to then display the results of.
Date truncation is a powerful tool used in Postgres to aggregate dates to gain powerful insights. With the rise of embedded analytics and internal Business Intelligence, understanding how Date Trunc works is becoming more important than ever. Hopefully, this piece helped to elucidate the nuances of date trunc for future use.
