oracle to_date with format doesn't show time

Question

I have simple calculation, I subtract interval from date with time:

 select TO_DATE('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS')  - to_dsinterval('00 0:05:00') from dual;

It works fine, the result: 2016-12-05 22:59:59

but it doesn't work correctly with timezones, so the next approach solves the problem with timezone. I just wrap expression with to_date() one more time

select TO_DATE(
TO_DATE('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS') - to_dsinterval('00 0:05:00')) from dual;

but now it turns time to zeros. Result should be: 2016-12-05 22:59:59 but actual: 2016-12-05 00:00:00

If I add format to the outer to_date as this:

select to_date( TO_DATE('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS') - to_dsinterval('00 0:05:00'), 'YYYY-MM-DD HH24:MI:SS') from dual;

The result become very strange: 0005-12-16 00:00:00 What I'm doing wrong?

Solution

DATE data type does not support any time zone functions, you must use TIMESTAMP WITH TIME ZONE for that.

Your query

SELECT TO_DATE( TO_DATE('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS') - TO_DSINTERVAL('00 0:05:00'), 'YYYY-MM-DD HH24:MI:SS') 
FROM dual;

does following:

1. Create a DATE '2016-12-05 23:04:59'
2. Subtract interval '00 0:05:00'
3. Cast to a VARCHAR2 (using NLS_DATE_FORMAT format)
4. Cast to a DATE using YYYY-MM-DD HH24:MI:SS format

In case your NLS_DATE_FORMAT would be equal to YYYY-MM-DD HH24:MI:SS this query returns correct output.

Use this one:

SELECT TO_TIMESTAMP('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS') - TO_DSINTERVAL('00 0:05:00')
FROM dual;

TO_DATE(... works as well. If you need time zone support you must do:

SELECT TO_TIMESTAMP_TZ('2016-12-05 23:04:59 Europe/Berlin', 'YYYY-MM-DD HH24:MI:SS TZR') - TO_DSINTERVAL('00 0:05:00')
FROM dual;

Comments

[Kacper]

Result of subtraction is already date. You don't need another to_date() and this not solves problem with time zones. If you want some operation on timezones please use timestamp at time zone

[MT0]

DATE dataypes can have INTERVAL DAY TO SECOND arithmetic performed on them. If you do SELECT DUMP( TO_DATE('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS') - TO_DSINTERVAL('00 0:05:00') ) FROM DUAL returns Typ=13 which is a DATE datatype so there is no implicit cast to a TIMESTAMP.