TRUNC - Truncate Datetime - Oracle to PostgreSQL Migration

In Oracle, the TRUNC(datetime, 'unit') function truncates a datetime value to the specified unit (for example, setting the time to zero or setting the date to the first day of the month). By default, it truncates to the day.

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, there is no default
  SELECT DATE_TRUNC('DAY', '2024-12-14 13:14:58'::TIMESTAMP);
  # 2024-12-14 00:00:00

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.