Oracle - Timestamp & Casting issues conversion to postgres fails?

[carloscondore]

Hello Team,

I wanted to know if this is expected or if there are other time functions that maybe are missing.

For example:

Original Oracle:
sysdate between to_date( f.C_SDATE,'yyyy/mm/dd' ) and to_date( f.C_EDATE ,'yyyy/mm/dd')

Postgres transpile:
CURRENT_TIMESTAMP BETWEEN STR_TO_DATE(f.C_SDATE, 'yyyy/mm/dd') AND STR_TO_DATE(f.C_EDATE, 'yyyy/mm/dd')
Error is showing STR_TO_DATE function does not exist.. which is true..

Correct transform:
current_date between to_date( f.C_SDATE,'yyyy/mm/dd' ) and to_date( f.C_EDATE ,'yyyy/mm/dd')

same with bellow:
In this one integer transformation is missing for each field

Original Oracle:
NVL((substr(r_TIMEOFFRULE.C_FTEDAYLEN,0,4)*86400+substr(substr(r_TIMEOFFRULE.C_FTEDAYLEN,0,7),-2)*3600+substr(substr(r_TIMEOFFRULE.C_FTEDAYLEN,0,10),-2)*60+substr(substr(r_TIMEOFFRULE.C_FTEDAYLEN,0,13),-2))/60,0)

Postgres transpile:
COALESCE(CAST((SUBSTR(r_TIMEOFFRULE.C_FTEDAYLEN, 0, 4) * 86400 + SUBSTR(SUBSTR(r_TIMEOFFRULE.C_FTEDAYLEN, 0, 7), -2) * 3600 + SUBSTR(SUBSTR(r_TIMEOFFRULE.C_FTEDAYLEN, 0, 10), -2) * 60 + SUBSTR(SUBSTR(r_TIMEOFFRULE.C_FTEDAYLEN, 0, 13), -2)) AS DOUBLE PRECISION) / 60,0)

Correct transform:
COALESCE((cast(substr(r_TIMEOFFRULE.C_FTEDAYLEN,0,4) as integer)*86400 +cast(substr(r_TIMEOFFRULE.C_FTEDAYLEN,6,2) as integer)*3600+cast(substr(r_TIMEOFFRULE.C_FTEDAYLEN,9,2) as integer)*60 +cast(substr(r_TIMEOFFRULE.C_FTEDAYLEN,12,2) as integer) )/60,0)

Is there specific way to add functions that exist in postgres and are not mapped to existing integration like:
Oracle:
TO_CHAR(FROM_TZ("dtl"."c_stime", 'GMT') AT TIME ZONE 'Europe/London', 'HH24:MI') AS "st"
Postgres:
TO_CHAR("dtl"."c_stime" AT TIME ZONE 'GMT' AT TIME ZONE 'Europe/London', 'HH24:MI') AS "st"

Or maybe pointing me in the right direction. :D

[georgesittas]

Hey @carloscondore,

I've addressed the first issue in #3124. Imo the second one is out of scope because it relies on Oracle's coercion rules and so you can do str * int which isn't generally supported in other dialects. Such transformations require type information and we usually don't prioritize them - you can use explicit casts in the original SQL as a workaround.

Check out said PR and also past PRs to see how we address similar issues. Feel free to make a PR for the last one if you want.

[carloscondore]

Hello @georgesittas thanks for the quick reply, will do!!