3

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?

Nikolas
  • 2,322
  • 9
  • 33
  • 55
  • 1
    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` – Kacper Dec 05 '16 at 14:26

2 Answers2

1

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;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • `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`. – MT0 Dec 05 '16 at 15:12
0

TO_DATE( char, fmt, nls ) takes VARCHAR2 arguments.

Performing TO_DATE('2016-12-05 23:04:59', 'YYYY-MM-DD HH24:MI:SS') - to_dsinterval('00 0:05:00') returns a DATE datatype which when you pass it to TO_DATE() oracle will cast it to a VARCHAR2 datatype so it matches the expected datatype of the argument (implicitly calling TO_CHAR( value, NLS_DATE_FORMAT ) to perform this cast) and then convert this back to a DATE datatype.

You just need to do:

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

If you want to handle time zones then use a TIMESTAMP AT TIME ZONE and just convert it to whatever timezone you want to store the date at:

SELECT TIMESTAMP '2016-12-05 23:04:59 Europe/Paris' AT TIME ZONE 'UTC'
FROM   DUAL;

(Will create your timestamp in Paris' time zone and convert it to the correct time in the UTC time zone).

MT0
  • 143,790
  • 11
  • 59
  • 117