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 */
create table starport_flights (
  id integer not null primary key,
  ship_id integer not null,
  arrival date default sysdate,
  departure date

create or replace package ut_starport as
  -- %suite(Starport functionality)

  -- %test(Ship gets Arrival date on insert)
  procedure ship_gets_default_arrival;

create or replace package body ut_starport as
  procedure ship_gets_default_arrival
    /* Expected arrival is the current date */
    l_expected_arrival date := current_date;
    l_actual_arrival date;
      /* Act */
      insert into starport_flights ( id, ship_id )
        values ( -1, -1 );

      /* Assert: Actual arrival should be within 5
         seconds more or less than the expected arrival */
      select arrival into l_actual_arrival
        from starport_flights where id = -1;

        .to_be_between( /* Assert with a bit of inaccuracy */
          l_expected_arrival - interval '5' second,
          l_expected_arrival + interval '5' second

call ut.run('ut_starport');
Starport functionality
   Ship gets Arrival date on insert [,312 sec] (FAILED - 1)


   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 seconds
1 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 */
create or replace procedure output_dates
  l_sysdate date := sysdate;
  l_dbtimezone varchar2(16) := dbtimezone;
  l_curdate date := current_date;
  l_sessiontimezone varchar2(16) := sessiontimezone;
      '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));

alter session set time_zone = '-6:00';
call output_dates();

alter session set time_zone = '+2:00';
call output_dates();
Sysdate (+00:00): 19:46, Current_Date (-06:00): 13:46, Difference (in hours): -6
Sysdate (+00:00): 19:46, Current_Date (+02:00): 21:46, Difference (in hours): 2

You can run this on LiveSQL.

The full example is available on GitHub.

BTW: Same goes for SYSTIMESTAMP and CURRENT_TIMESTAMP. Thanks Jacek for remembering me.


Ben Einhorn · December 18, 2018 at 11:32 pm

So,what do you think about using UTC dates versus not using UTC dates? 🙂

    Pesse · December 18, 2018 at 11:39 pm

    It depends 🙂
    The safest option is probably not to use DATE as datatype but use TIMESTAMP WITH TIME ZONE for example. That case you have all the information available if you need them.
    If you happen to be in the situation where you can’t change the datatype, I’d suggest to constantly use SYSDATE (server’s timezone) over CURRENT_DATE where possible. Server should always be the final source of truth.
    I wouldn’t do the increased effort to manually convert SYSDATE-dates to UTC if your DB time zone is set up differently. Either use TIMESTAMP WITH TIME ZONE or use the DB time zone.

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.