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
Original issue reported on code.google.com by
amoebawo...@gmail.com
on 18 Mar 2015 at 5:30