TRUNC - Truncate Datetime - Oracle to PostgreSQL Migration

In Oracle, the TRUNC(datetime, 'unit') function truncates a datetime value to the specified unit (set zero time, set the first day of the month i.e).

In PostgreSQL, you can use the DATE_TRUNC function, but note that order of parameters and unit values are different.

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- By default, TRUNC truncates to day (sets zero time)
  SELECT TRUNC(TO_DATE('2024-12-14 13:14:58')) FROM dual;
  # 2024-12-14 00:00:00

PostgreSQL:

  -- DATE_TRUNC requires unit to be specified, no default
  SELECT DATE_TRUNC('DAY', '2024-12-14 13:14:58'::TIMESTAMP);
  # 2024-12-14 00:00:00

TRUNC Conversion Overview

Oracle TRUNC for datetime to PostgreSQL conversion:

Oracle PostgreSQL
Syntax TRUNC(datetime[, 'unit']) TRUNC('unit', datetime)
Default Unit 'DD' truncates to day (sets zero time) No default, unit must be specified

Converting specific units:

Oracle TRUNC Unit Truncation PostgreSQL DATE_TRUNC Unit Result
'DD' 'DDD' Day 'DAY' YYYY-MM-DD 00:00:00
'MM' 'MONTH' 'MON' Month 'MONTH' YYYY-MM-01 00:00:00
'YY' 'YYYY' 'YEAR' Year 'YEAR' YYYY-01-01 00:00:00
'HH' 'HH24' Hour 'HOUR' YYYY-MM-DD HH:00:00
'MI' Minute 'MINUTE' YYYY-MM-DD HH:MI:00

For more information, see Oracle to PostgreSQL Migration.