SQL DATE_TRUNC()

The DATE_TRUNC() function is used to truncate a date, time, or timestamp to a specified interval, such as the day, week, or month, in PostgreSQL and SQL Server.

The DATE_TRUNC() function is particularly useful for time series analysis to understand how a value changes over time. Practical examples would include analyzing company’s quarterly sales or determining the average hourly temperature.

Note: The syntax varies slightly by SQL flavor; in PostgreSQL, the function is DATE_TRUNC(), while SQL Server uses the DATETRUNC() function without an underscore. MySQL does not have a DATE_TRUNC() function, but MySQL’s EXTRACT() function is a commonly used alternative.

PostgreSQL Syntax

DATE_TRUNC(interval, date)

The DATE_TRUNC() function in PostgreSQL has two required parameters:

• interval is the date or time interval to which the date will be truncated. Any of the following dateparts are valid inputs:
  • microseconds
  • milliseconds
  • second
  • minute
  • hour
  • day
  • week
  • month
  • quarter
  • year
  • decade
  • century
  • millennium
• date is the date, time, or timestamp to truncate.

By default, the date will be truncated with respect to its current time zone.

Example 1

The following example truncates 2023-07-23 07:08:30 by year:

SELECT DATE_TRUNC('year', '2023-07-23 07:08:30');
/* Output: 2023-01-01 00:00:00 */

Copy to clipboard
Copy to clipboard

Example 2

The following example truncates 2023-07-23 07:08:30 by minute:

SELECT DATE_TRUNC('minute', '2023-07-23 07:08:30');
/* Output: 2023-07-23 07:08:00 */

Copy to clipboard
Copy to clipboard

Example 3

Assume there is a table called fruit_sales with the following data:

The DATE_TRUNC() function can be used to summarize how much money the fruit stand is making each hour of a given day:

SELECT DATE_TRUNC('hour', sale_date) as fruit_sale_hour_at,
   SUM(sale_price) AS sum_of_fruit_sales
FROM fruit_sales
GROUP BY fruit_sale_hour_at

Copy to clipboard
Copy to clipboard

This returns the sale_date, truncated by hour, as fruit_sale_hour_at, and the total cost of the fruit sold during that hour:

Based on this analysis, the fruit stand made more money ($3.50) from 11 am to noon than in the previous hour, from 10 am to 11 am ($2.50).

SQL Server Syntax

DATETRUNC(interval, date)

The DATETRUNC() function in SQL Server has the same functionality and required parameters as DATE_TRUNC() in PostgreSQL, but does not have an underscore in the function name.

Example - SQL Server Variant

The following example truncates 2023-07-23 07:08:30 by year in SQL Server:

SELECT DATETRUNC('year', '2023-07-23 07:08:30');
/* Output: 2023-01-01 00:00:00 */

Copy to clipboard
Copy to clipboard