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
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.