1.4.186: Daylight savings time and date math

[GoogleCodeExporter]

Running this query after daylight savings time to get a date before daylight 
saving time. For example: Today is: Mar 18, 2015

Query:
select trunc(SYSDATE) - cast(18 AS NUMBER) from DUAL

Expected output:
DATEADD('DAY', -18, TRUNC(CURRENT_TIMESTAMP()))  
2015-02-28 00:00:00.0

Actual output:
DATEADD('SECOND', -1555200, TRUNC(CURRENT_TIMESTAMP()))  
2015-02-27 23:00:00.0

When doing the cast to a number, the date math changes from expected DAY to 
actual SECONDs but it doesn't take into account daylight savings. 1555200 = 18 
(days) * 24 (hrs) * 60 (mins) * 60 (secs)

What version of the product are you using? On what operating system, file
system, and virtual machine?
1.4.186. Windows 7. Java 1.7.0_75

Do you know a workaround?

Workaround #1 (best):
select trunc(SYSDATE) - 18 from DUAL
DATEADD('DAY', -18, TRUNC(CURRENT_TIMESTAMP()))  
2015-02-28 00:00:00.0

Workaround #2 (not so good):
select trunc(SYSDATE - cast(18 AS NUMBER)) from DUAL;
TRUNC(DATEADD('SECOND', -1555200, CURRENT_TIMESTAMP()))  
2015-02-28 00:00:00.0

Original issue reported on code.google.com by amoebawo...@gmail.com on 18 Mar 2015 at 5:30

[GoogleCodeExporter]

Why don't you just use dateadd directly? That would be much cleaner.

Using "-" with a number is really just a compatibility feature for Oracle. I 
don't think you should rely on that, specially because the number is ambiguous 
(is it days? is it hours or seconds?). Maybe I should just remove that feature.

I'm going to close this issue as "won't fix". If you don't agree, please open a 
discussion on the mailing list and get some supporters for your idea, and a 
patch. We should also have a way to verify this is really what Oracle does (or 
other databases) in this case.

Original comment by thomas.t...@gmail.com on 25 Mar 2015 at 7:19

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 25 Mar 2015 at 7:19

• Changed state: WontFix