Oracle DATE vs PostgreSQL TIMESTAMP: Avoid the Traps

Oracle DATE Is Not PostgreSQL DATE

It looks like a clean one-to-one mapping — both databases have a DATE type, so line them up and move on, right? That single assumption silently corrupts data. An Oracle DATE always carries a time component, down to the second. A PostgreSQL DATE is date-only. Map one to the other and every hour, minute, and second in the column is quietly dropped — while row counts still report a clean, successful migration.

See It For Yourself

-- Oracle: DATE includes the time of day
INSERT INTO orders (created)
  VALUES (TO_DATE('2026-06-12 14:30:05','YYYY-MM-DD HH24:MI:SS'));
SELECT TO_CHAR(created,'YYYY-MM-DD HH24:MI:SS') FROM orders;  -- 2026-06-12 14:30:05

-- PostgreSQL DATE: the time is gone the moment you store it
INSERT INTO orders (created) VALUES (TIMESTAMP '2026-06-12 14:30:05');  -- into a DATE column
SELECT created FROM orders;   -- 2026-06-12   (14:30:05 silently dropped)

Same value, same intent — but the PostgreSQL DATE threw away the clock. The fix is to map Oracle DATE to PostgreSQL TIMESTAMP, not DATE.

The Correct Type Mapping

The Date-Arithmetic Trap

Even with the right column types, the expressions around them differ. In Oracle a date plus a number adds days; in PostgreSQL you add an INTERVAL. Subtracting two dates returns a number of days in Oracle but an INTERVAL in PostgreSQL. Any code that does its own date math has to change — or it won't compile, or worse, will quietly misbehave.

-- Oracle: bare number arithmetic means days
SELECT created + 7    FROM orders;   -- 7 days later
SELECT due - created  FROM orders;   -- a NUMBER of days (e.g. 3.5)

-- PostgreSQL: use INTERVAL; subtraction yields an INTERVAL
SELECT created + INTERVAL '7 days'  FROM orders;
SELECT due - created                FROM orders;   -- INTERVAL '3 days 12:00:00'
SELECT EXTRACT(EPOCH FROM (due - created)) / 86400 AS days FROM orders;  -- back to a number

Function & Default Swaps

SYSDATE               ->  CURRENT_TIMESTAMP     -- or LOCALTIMESTAMP (no time zone)
SYSTIMESTAMP          ->  now()                 -- timestamptz, carries the zone
TRUNC(d)              ->  date_trunc('day', d)
TRUNC(d, 'MM')        ->  date_trunc('month', d)
ADD_MONTHS(d, n)      ->  d + (n || ' months')::interval
LAST_DAY(d)           ->  (date_trunc('month', d) + INTERVAL '1 month - 1 day')::date
TO_DATE(s, fmt)       ->  to_timestamp(s, fmt)  -- PG's to_date() returns a DATE (no time!)

Watch the last line: PostgreSQL has a to_date(), but it returns a date-only value. When the Oracle source used TO_DATE to parse a string that includes a time, the faithful translation is to_timestamp() — another easy way to lose the clock.

Time Zones: TIMESTAMPTZ Is Stored as UTC

Oracle's TIMESTAMP WITH TIME ZONE maps to PostgreSQL TIMESTAMPTZ, which normalizes every value to UTC on the way in and renders it in the session's time zone on the way out. Set the session zone explicitly during the load so values land where you expect, and prefer TIMESTAMPTZ for anything that crosses regions — a plain TIMESTAMP has no zone and is read literally.

SET TIME ZONE 'UTC';   -- make the load deterministic
-- Oracle TIMESTAMP WITH TIME ZONE  ->  PostgreSQL TIMESTAMPTZ (stored UTC)
-- Oracle TIMESTAMP (no zone)        ->  PostgreSQL TIMESTAMP   (no zone — read literally)

Format Masks & the RR Year

Most TO_CHAR / TO_DATE format masks carry over unchanged (YYYY, MM, DD, HH24, MI, SS). Two to audit: Oracle's RR / RRRR two-digit-year pivot has no direct PostgreSQL equivalent (normalize to four-digit years on the way in), and fractional-second masks (FF vs US / MS) differ — check anywhere sub-second precision matters.

Why Only Column-Level Validation Catches This

A botched DATE mapping is invisible to row counts — the same number of rows arrives whether the time component survived or not. Only column-level validation — a checksum or per-partition Merkle fingerprint over the actual values — flags a column where 14:30:05 became 00:00:00. If your validation is count-only, this ships to production and resurfaces weeks later as "why is every timestamp at midnight?"

How DBMigrateAIPro Handles It

The tool maps Oracle DATE to PostgreSQL TIMESTAMP by default (never date-only), flags TIMESTAMP WITH TIME ZONE columns for TIMESTAMPTZ, rewrites SYSDATE / TRUNC / ADD_MONTHS and the date arithmetic during PL/SQL conversion, and then validates at the column level with Merkle fingerprints — so a dropped time component shows up as a DATA MISMATCH with the exact rows, not a silent success.