Oracle to Postgres SQL Translation: DATE and TIMESTAMP Are Not the Same Type

The Type Mismatch That Migrations Get Wrong

Oracle's DATE datatype stores year, month, day, hour, minute, and second. It has always done this. The name is misleading by modern standards — it's effectively a datetime type with second precision. When an Oracle developer inserts SYSDATE into a DATE column, they're storing the full timestamp. When they compare two DATE values, they're comparing timestamps. When they do DATE arithmetic, they're doing datetime arithmetic.

PostgreSQL has both a DATE type (date only, no time) and a TIMESTAMP type (date and time). The correct migration target for Oracle DATE columns is PostgreSQL TIMESTAMP — specifically TIMESTAMP(0) to match Oracle's second-level precision.

ora2pg maps Oracle DATE to PostgreSQL TIMESTAMP by default, which is correct. The problem occurs in three places that tools don't automatically fix: SQL expressions involving date arithmetic, application code that constructs date literals, and stored procedures that use Oracle date functions with no direct Postgres equivalent.

Date Arithmetic: The Subtraction Problem

In Oracle, subtracting two DATE values returns a NUMBER representing fractional days:

-- Oracle: DATE subtraction returns NUMBER (fractional days)
SELECT
  end_date - start_date AS days_elapsed,        -- e.g., 2.5 (2 days, 12 hours)
  TRUNC(end_date - start_date) AS full_days,     -- e.g., 2
  (end_date - start_date) * 24 AS hours_elapsed -- e.g., 60
FROM projects;

-- Oracle: Add days to a DATE
SELECT start_date + 7 AS one_week_later FROM projects;  -- adds 7 days
SELECT start_date + 1/24 AS one_hour_later FROM projects; -- adds 1 hour

In PostgreSQL, subtracting two TIMESTAMP values returns an INTERVAL, not a number. Subtracting two DATE values also returns an INTEGER (whole days). The arithmetic syntax changes completely:

-- PostgreSQL: TIMESTAMP subtraction returns INTERVAL
SELECT
  end_date - start_date AS interval_elapsed,      -- e.g., '2 days 12:00:00'
  EXTRACT(EPOCH FROM (end_date - start_date)) / 86400 AS days_elapsed,  -- fractional days as float
  DATE_PART('day', end_date - start_date) AS full_days,
  EXTRACT(EPOCH FROM (end_date - start_date)) / 3600 AS hours_elapsed
FROM projects;

-- PostgreSQL: Add days to a TIMESTAMP
SELECT start_date + INTERVAL '7 days' AS one_week_later FROM projects;
SELECT start_date + INTERVAL '1 hour' AS one_hour_later FROM projects;

-- Or using integer + type casting (for simple day addition):
SELECT start_date + 7 AS one_week_later FROM projects;
-- This works only if start_date is DATE type, not TIMESTAMP
-- For TIMESTAMP columns, integer addition is not defined

Any Oracle SQL that does date_column + N where N is a numeric expression needs to be rewritten as date_column + INTERVAL '...' or date_column + (N || ' days')::INTERVAL if N is a variable.

-- Oracle: variable day addition
SELECT order_date + promised_days AS due_date FROM orders;

-- PostgreSQL equivalent (promised_days is INTEGER column):
SELECT order_date + (promised_days || ' days')::INTERVAL AS due_date FROM orders;
-- Or:
SELECT order_date + make_interval(days => promised_days) AS due_date FROM orders;

SYSDATE vs CURRENT_TIMESTAMP

Oracle's SYSDATE returns the current database server date and time as a DATE (no fractional seconds). Oracle's SYSTIMESTAMP returns the current timestamp with fractional seconds and timezone.

PostgreSQL equivalents:

-- Oracle SYSDATE → PostgreSQL CURRENT_TIMESTAMP or NOW()
-- Both return TIMESTAMP WITH TIME ZONE in Postgres

-- If the Oracle column being compared is DATE (migrated as TIMESTAMP):
-- Use CURRENT_TIMESTAMP::TIMESTAMP or NOW()::TIMESTAMP to strip timezone

-- Oracle:
WHERE created_date >= SYSDATE - 7

-- PostgreSQL equivalent:
WHERE created_date >= NOW() - INTERVAL '7 days'
-- or:
WHERE created_date >= CURRENT_TIMESTAMP - INTERVAL '7 days'

The timezone issue is subtle. SYSDATE in Oracle returns the database server's local time with no timezone information — it's a naive datetime. PostgreSQL's NOW() and CURRENT_TIMESTAMP return TIMESTAMP WITH TIME ZONE. If your migrated columns are TIMESTAMP WITHOUT TIME ZONE (the correct target for Oracle DATE), comparisons between the column and NOW() require an explicit cast:

-- Explicit cast to avoid implicit timezone conversion issues
WHERE created_date >= NOW()::TIMESTAMP WITHOUT TIME ZONE - INTERVAL '7 days'
-- or configure timezone consistently in postgresql.conf and rely on implicit cast

TRUNC on Dates: Different Default Behavior

Oracle's TRUNC(date) truncates a DATE to midnight (removes the time component). It's heavily used in Oracle date comparison patterns:

-- Oracle: common pattern for "today's records"
SELECT * FROM orders WHERE TRUNC(order_date) = TRUNC(SYSDATE);

-- Oracle: "this month's records"
SELECT * FROM orders WHERE TRUNC(order_date, 'MM') = TRUNC(SYSDATE, 'MM');

PostgreSQL has no TRUNC function for timestamps. The equivalent is DATE_TRUNC, with different syntax:

-- PostgreSQL equivalents:

-- Truncate to day (midnight)
DATE_TRUNC('day', order_date)

-- Truncate to month start
DATE_TRUNC('month', order_date)

-- "Today's records" in PostgreSQL:
SELECT * FROM orders
WHERE order_date >= DATE_TRUNC('day', NOW()::TIMESTAMP)
  AND order_date < DATE_TRUNC('day', NOW()::TIMESTAMP) + INTERVAL '1 day';

-- Or using BETWEEN (inclusive both ends — be careful with TIMESTAMP):
SELECT * FROM orders
WHERE order_date::DATE = CURRENT_DATE;
-- This cast to DATE drops time component — works but disables index use on TIMESTAMP columns
-- Better to use the range form above to keep index eligibility

The equality comparison pattern (TRUNC(col) = TRUNC(SYSDATE)) deserves special attention: in Oracle, this is indexable via a function-based index on TRUNC(order_date). In PostgreSQL, order_date::DATE = CURRENT_DATE requires a functional index on (order_date::DATE) to be index-eligible. The range form (order_date >= x AND order_date < y) is index-eligible without a functional index if a standard B-tree index on order_date exists.

ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY

Oracle date functions with no direct Postgres name equivalents:

-- Oracle ADD_MONTHS
ADD_MONTHS(hire_date, 6)

-- PostgreSQL:
hire_date + INTERVAL '6 months'
-- or for variable months (n is an integer):
hire_date + make_interval(months => n)

-- Oracle MONTHS_BETWEEN (returns fractional months)
MONTHS_BETWEEN(end_date, start_date)

-- PostgreSQL: no direct equivalent; approximate with:
EXTRACT(YEAR FROM AGE(end_date, start_date)) * 12
+ EXTRACT(MONTH FROM AGE(end_date, start_date))
-- Note: AGE() returns an interval; this extracts only full months
-- For fractional months matching Oracle's exact behavior, use:
(EXTRACT(EPOCH FROM (end_date - start_date)) / (365.25/12 * 86400))

-- Oracle LAST_DAY
LAST_DAY(order_date)  -- last day of the month containing order_date

-- PostgreSQL:
DATE_TRUNC('month', order_date) + INTERVAL '1 month' - INTERVAL '1 day'
-- or:
(DATE_TRUNC('month', order_date) + INTERVAL '1 month - 1 day')::DATE

TO_DATE and TO_CHAR Format Strings

Oracle's TO_DATE maps to PostgreSQL's TO_TIMESTAMP for datetime values (since Oracle DATE has time). Format strings are mostly compatible but have important differences:

-- Oracle format strings → PostgreSQL equivalents
-- Oracle: YYYY-MM-DD HH24:MI:SS  → PostgreSQL: YYYY-MM-DD HH24:MI:SS (same)
-- Oracle: DD-MON-YYYY            → PostgreSQL: DD-Mon-YYYY (case matters in PG)
-- Oracle: J (Julian date)        → PostgreSQL: J (same)
-- Oracle: SSSSS (seconds past midnight) → PostgreSQL: SSSSS (same)
-- Oracle: FF (fractional seconds) → PostgreSQL: MS (milliseconds) or US (microseconds)
-- Oracle: TZR (timezone region)  → PostgreSQL: TZ (timezone abbreviation only)

-- Oracle:
TO_DATE('2025-01-14 09:30:00', 'YYYY-MM-DD HH24:MI:SS')

-- PostgreSQL:
TO_TIMESTAMP('2025-01-14 09:30:00', 'YYYY-MM-DD HH24:MI:SS')
-- Returns TIMESTAMP WITH TIME ZONE; cast to TIMESTAMP if column is without TZ:
TO_TIMESTAMP('2025-01-14 09:30:00', 'YYYY-MM-DD HH24:MI:SS')::TIMESTAMP

Auditing for DATE/TIMESTAMP Issues Before Cutover

Run this pattern against your migrated SQL corpus to find date arithmetic that needs manual review:

# Find Oracle-style date arithmetic in SQL files
grep -rn --include="*.sql" \
  -E "(SYSDATE|ADD_MONTHS|MONTHS_BETWEEN|LAST_DAY|TRUNC\(.*DATE|date_col [+-] [0-9])" \
  ./migrated_sql/ | sort | uniq -c | sort -rn | head -50

Every match is a candidate for incorrect behavior in PostgreSQL. The migration tools convert the column types correctly. They do not automatically rewrite all SQL expressions that depend on Oracle's DATE-as-datetime semantics. That audit is manual engineering work, and it's proportional to how heavily the codebase uses Oracle date functions — which, in Oracle shops that have been running for 10+ years, is usually very heavily.