I believe I misunderstand something, but not sure where it is. I am trying to convert my DATE data from ET to MX, using the query below:
with time_in_ny as(
select to_date('04/28/2025 00:12:52', 'MM/DD/YYYY HH24:MI:SS') trans_date from dual
union all
select to_date('04/28/2025 11:18:34', 'MM/DD/YYYY HH24:MI:SS') trans_date from dual
union all
select to_date('04/28/2025 01:25:37', 'MM/DD/YYYY HH24:MI:SS') trans_date from dual
)
select trans_date,
FROM_TZ(CAST(trans_date AS TIMESTAMP),'America/New_York') AT TIME ZONE 'America/Mexico_City' tz_convert_mx,
trunc(FROM_TZ(CAST(trans_date AS TIMESTAMP),'America/New_York') AT TIME ZONE 'America/Mexico_City') tz_convert_mx_trunc
from time_in_ny
order by trans_date;
The conversion seems ok from column TRANS_DATE to TZ_CONVERT_MX (Mexico City is two hours behind New York), but when I truncate column TZ_CONVERT_MX in order to get the converted date (without time/time zone), one row (row 2 ) seems incorrect.
TRANS_DATE TZ_CONVERT_MX TZ_CONVERT_MX_TRUNC
------------------- --------------------------------------------------- -------------------
04/28/2025 00:12:52 27-APR-25 10.12.52.000000000 PM AMERICA/MEXICO_CITY 04/27/2025 00:00:00
04/28/2025 01:25:37 27-APR-25 11.25.37.000000000 PM AMERICA/MEXICO_CITY 04/28/2025 00:00:00
04/28/2025 11:18:34 28-APR-25 09.18.34.000000000 AM AMERICA/MEXICO_CITY 04/28/2025 00:00:00
I expected row 2 will give me 04/27/2025 at column TZ_CONVERT_MX_TRUNC but somehow it is 04/28/2025.
Any pointer is greatly appreciated.
Thanks,
Vu
PS: this runs on an Oracle 11.2.0.4/Linux. My session is in American/Chicago time zone.