SQL DATE_TRUNC Function

Summary: In this tutorial, you’ll learn how to use the SQL DATE_TRUNC function to truncate a date, time, or timestamp.

Introduction to SQL DATE_TRUNC Function #

The DATE_TRUNC function allows you to truncate a date, time, or timestamp to a specified precision.

Here’s the syntax of the DATE_TRUNC function:

DATE_TRUNC(unit, date)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• unit: The unit of time you want to truncate the date. The unit can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
• date: The date, time, or timestamp you want to truncate.

The DATE_TRUNC function can be helpful for time series analysis and data aggregation.

SQL DATE_TRUNC function examples #

Let’s use the employees table from the HR sample database to demonstrate the DATE_TRUNC function:

Truncating to the Start of the Year #

The following statement uses the DATE_TRUNC function to truncate values in the hire_date column of employees to the start of the year:

SELECT
  employee_id,
  first_name,
  last_name,
  hire_date,
  DATE_TRUNC('YEAR', hire_date) AS hire_year_start
FROM
  employees
ORDER BY
  hire_year_start;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 employee_id | first_name  |  last_name  | hire_date  | hire_year_start
-------------+-------------+-------------+------------+-----------------
         200 | Jennifer    | Whalen      | 1987-09-17 | 1987-01-01
         100 | Steven      | King        | 1987-06-17 | 1987-01-01
         101 | Neena       | Kochhar     | 1989-09-21 | 1989-01-01
         103 | Alexander   | Hunold      | 1990-01-03 | 1990-01-01
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returns the employee ID, first name, last name, original hire date, and the start of the year of the hire date:

You can use the data from the hire_year_start column to calculate the headcount at the beginning of each year.

Grouping Employees by the Starting of the Year #

The following statement uses the DATE_TRUNC function to truncate the hire datethe  to start of the year and uses the GROUP BY clause with the COUNT function to calculate headcount for each start of year:

SELECT
  DATE_TRUNC('YEAR', hire_date) AS hire_year_start,
  COUNT(*) AS headcount
FROM
  employees
GROUP BY
  hire_year_start
ORDER BY
  hire_year_start;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 hire_year_start | headcount
-----------------+-----------
 1987-01-01      |         2
 1989-01-01      |         1
 1990-01-01      |         1
 1991-01-01      |         1
 1993-01-01      |         1
 1994-01-01      |         7
 1995-01-01      |         2
 1996-01-01      |         4
 1997-01-01      |        10
 1998-01-01      |         6
 1999-01-01      |         4
 2000-01-01      |         1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Truncating to the Start of the Month #

The following example uses the DATE_TRUNC function to truncate the hire_date to the start of the month:

SELECT
  employee_id,
  first_name,
  last_name,
  hire_date,
  DATE_TRUNC('MONTH', hire_date) AS hire_month_start
FROM
  employees
ORDER BY
  hire_month_start;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

employee_id | first_name  |  last_name  | hire_date  | hire_month_start
-------------+-------------+-------------+------------+------------------
         100 | Steven      | King        | 1987-06-17 | 1987-06-01
         200 | Jennifer    | Whalen      | 1987-09-17 | 1987-09-01
         101 | Neena       | Kochhar     | 1989-09-21 | 1989-09-01
         103 | Alexander   | Hunold      | 1990-01-03 | 1990-01-01
         104 | Bruce       | Ernst       | 1991-05-21 | 1991-05-01
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returns the employee ID, first name, last name, original hire date, and the start of the month of the hire date:

You can use the data in the hire_month_start for calculating the number of new employees on payroll for starting from the beginning of the hire month.

Grouping Employees by Hire Month #

The following statement uses the DATE_TRUNC to count employees by the start of the hired month:

SELECT
  DATE_TRUNC('MONTH', hire_date)::DATE AS hire_month,
  COUNT(*) AS new_employee_count
FROM
  employees
GROUP BY
  hire_month
ORDER BY
  hire_month;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 hire_month | new_employee_count
------------+--------------------
 1987-06-01 |                  1
 1987-09-01 |                  1
 1989-09-01 |                  1
 1990-01-01 |                  1
 1991-05-01 |                  1
 1993-01-01 |                  1
 1994-06-01 |                  4
 1994-08-01 |                  2
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the SQL DATE_TRUNC function for truncating dates and times to a specified precision.