Posted on • Originally published atcleandatabase.wordpress.com on
#100CodeExamples – SYSDATE vs. CURRENT_DATE
I ran into a strange error yesterday: when I ran my test-suite from IDE I suddenly got a number of failed tests, but they were completely okay when I ran it from utPLSQL-cli.
I panicked a bit because that’s one of the worst possible situations you can imagine for a testing framework.
After some investigation, I found the problem.
Look at the following example-test suite which assures that the current date and time is set for “arrival” when a new entry is inserted into the “starport_flights”-table:
/* Simple table which contains starship-flights */createtablestarport_flights(idintegernotnullprimarykey,ship_idintegernotnull,arrivaldatedefaultsysdate,departuredate);createorreplacepackageut_starportas-- %suite(Starport functionality)-- %test(Ship gets Arrival date on insert)procedureship_gets_default_arrival;end;createorreplacepackagebodyut_starportasprocedureship_gets_default_arrivalas/* Expected arrival is the current date */l_expected_arrivaldate:=current_date;l_actual_arrivaldate;begin/* Act */insertintostarport_flights(id,ship_id)values(-1,-1);/* Assert: Actual arrival should be within 5 seconds more or less than the expected arrival */selectarrivalintol_actual_arrivalfromstarport_flightswhereid=-1;ut.expect(l_actual_arrival).to_be_between(/* Assert with a bit of inaccuracy */l_expected_arrival-interval'5'second,l_expected_arrival+interval'5'second);end;end;/callut.run('ut_starport');Starport functionality Ship gets Arrival date on insert [,312 sec] (FAILED - 1)Failures: 1) ship_gets_default_arrival Actual: 2018-12-18T19:47:25 (date) was expected to be between: 2018-12-18T21:47:20 and 2018-12-18T21:47:30 at "SITHDB.UT_STARPORT.SHIP_GETS_DEFAULT_ARRIVAL", line 16 ut.expect(l_actual_arrival)Finished in ,312538 seconds1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)Looks like the session in my IDE had a different timezone set than my database, while the fresh session in utPLSQL-cli had the same timezone as the database.
Depending on what you want to achieve, you should use SYSDATE or CURRENT_DATE consistently.
The following little function shows how those two differ:
/* Simple procedure to output sysdate and current_date Difference is CURRENT_DATE - SYSDATE */createorreplaceprocedureoutput_datesasl_sysdatedate:=sysdate;l_dbtimezonevarchar2(16):=dbtimezone;l_curdatedate:=current_date;l_sessiontimezonevarchar2(16):=sessiontimezone;begindbms_output.PUT_LINE('Sysdate ('||l_dbtimezone||'): '||to_char(l_sysdate,'HH24:MI')||', Current_Date ('||l_sessiontimezone||'): '||to_char(l_curdate,'HH24:MI')||', Difference (in hours): '||to_char((l_curdate-l_sysdate)*24));end;/altersessionsettime_zone='-6:00';calloutput_dates();altersessionsettime_zone='+2:00';calloutput_dates();Sysdate (+00:00): 19:46, Current_Date (-06:00): 13:46, Difference (in hours): -6Sysdate (+00:00): 19:46, Current_Date (+02:00): 21:46, Difference (in hours): 2You can run this onLiveSQL.
The full example is available onGitHub.
Top comments(0)
For further actions, you may consider blocking this person and/orreporting abuse
