dates

Truncating a timestamp to the second

Connor McDonald , , ,

We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value.  At first glance, TRUNC seems fine.


SQL> select trunc(localtimestamp,'MM') to_the_month from dual;

TO_THE_MO
---------
01-OCT-16

1 row selected.

SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;

TO_THE_YE
---------
01-JAN-16

1 row selected.

SQL> select trunc(localtimestamp,'DD') to_the_day from dual;

TO_THE_DA
---------
20-OCT-16

1 row selected.

But the moment you try apply a TRUNC down to the second, then things go wrong


SQL> select trunc(localtimestamp,'SS') to_the_day from dual;
select trunc(localtimestamp,'SS') to_the_day from dual
                            *
ERROR at line 1:
ORA-01899: bad precision specifier

The clue here is if we look at the DUMP output for some of these commands


SQL> select dump(localtimestamp) from dual;

DUMP(LOCALTIMESTAMP)
-----------------------------------------------------------------------
Typ=187 Len=20: 224,7,10,20,13,48,45,0,192,3,180,35,8,0,3,0,0,0,0,0

1 row selected.

SQL> select dump(trunc(localtimestamp,'YYYY')) dmp from dual;

DMP
-----------------------------------------------------------------------
Typ=13 Len=8: 224,7,1,1,0,0,0,0

1 row selected.

Notice that the datatype has changed. In fact, type 13 is the same datatype as we see for SYSDATE


SQL> select dump(sysdate) from dual;

DUMP(SYSDATE)
-------------------------------------------
Typ=13 Len=8: 224,7,10,20,13,50,1,0

If you look in the SQL documentation, you’ll see that there actually is NOT a trunc command for timestamps. So what is in fact happening is:

  • the timestamp is being silently converted to a date,
  • the trunc command is being applied to the date

and just like any date, SS is not an appropriate TRUNC mask.


SQL> select trunc(sysdate,'SS') from dual;
select trunc(sysdate,'SS') from dual
                     *
ERROR at line 1:
ORA-01899: bad precision specifier

A simple workaround is to convert the timestamp to a date, and if necessary, convert it back to a timestamp to preserve the datatype.


SQL> select localtimestamp ts, cast(localtimestamp as date) truncd_to_secs from dual;

TS                                       TRUNCD_TO_SECS
---------------------------------------- -------------------
20-OCT-16 01.54.09.991000 PM             20/10/2016 13:54:09

SQL> select dump(cast(cast(localtimestamp as date) as timestamp)) from dual;

DUMP(CAST(CAST(LOCALTIMESTAMPASDATE)ASTIMESTAMP))
-----------------------------------------------------------------------------
Typ=187 Len=20: 224,7,10,20,13,54,45,0,0,0,0,0,0,0,3,0,0,0,0,0

The simple fix to date queries

Connor McDonald , ,

We had question in the OpenWorld panel about why queries on date columns are “always slow”.  Well….they aren’t Smile but here’s a common cause of that misconception.

Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is  a date).


SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL>
SQL> create index t_ix on t ( created );

Index created.

The problems start when we do a query on the CREATED column and get “unexpected” results


SQL> select owner, object_name
  2  from t
  3  where created = date '2016-09-20';

no rows selected

We were expecting to get some rows here, but none were returned. And we quickly deduce that this is because of the CREATED column also containing the time component. So nothing was created at midnight on September 20.

So to remove the time component, the query is recast as:


SQL> set autotrace on
SQL> select owner, object_name
  2  from t
  3  where trunc(created) = date '2016-09-20';

OWNER        OBJECT_NAME
------------ ------------------------------
MCDONAC      T1


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   951 | 38040 |   446   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   951 | 38040 |   446   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2016-09-20
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1602  consistent gets
          0  physical reads
          0  redo size
        620  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and ker-splat, the ability to use the index on the CREATED column disappears because we put the TRUNC expression around it. We used 1602 logical I/O’s to satisfy our query. But all it takes is a little tinkering of our query to get CREATED “unwrapped”


SQL> select owner, object_name
  2  from t
  3  where created >= date '2016-09-20'
  4  and created < date '2016-09-20' + 1;

OWNER        OBJECT_NAME
------------ ------------------------------
MCDONAC      T1


Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |   184 |  7360 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |   184 |  7360 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IX |   184 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATED">=TO_DATE(' 2016-09-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "CREATED"< processed rows 1 (disk) sorts 0 (memory) client from to roundtrips SQL*Net 2 via received bytes 552 sent 620 size redo reads physical gets consistent 4 block db calls recursive ---------------------------------------------------------- Statistics hh24:mi:ss?)) ?syyyy-mm-dd 00:00:00?, 2016-09-21>

The query is the same functionally, but we got to use the index to speed up our query.  This is not by any means claiming that using the index is always the best option, but at least by having the CREATED column “untarnished” by expressions, then we are giving the optimizer more choices on the potential means to best run the query.

Datatypes for DATES

Connor McDonald ,

Richard Foote has written a post about not using the DATE datatype for storing dates.

So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype.

Here’s a demo


SQL> create table t ( x raw(7) );

Table created.

SQL>
SQL> create or replace
  2  procedure store_date(p_yyyymmddhh24miss varchar2) is
  3  begin
  4    insert into t
  5    values
  6      (
  7        hextoraw(
  8         to_char(to_number(substr(p_yyyymmddhh24miss,1,2))+100,'FM0X')||
  9         to_char(to_number(substr(p_yyyymmddhh24miss,3,2))+100,'FM0X')||
 10         to_char(to_number(substr(p_yyyymmddhh24miss,5,2)),'FM0X')||
 11         to_char(to_number(substr(p_yyyymmddhh24miss,7,2)),'FM0X')||
 12         to_char(to_number(substr(p_yyyymmddhh24miss,9,2))+1,'FM0X')||
 13         to_char(to_number(substr(p_yyyymmddhh24miss,11,2))+1,'FM0X')||
 14         to_char(to_number(substr(p_yyyymmddhh24miss,13,2))+1,'FM0X')
 15        )
 16      );
 17  end;
 18  /

Procedure created.

SQL>
SQL> exec store_date('20160528211212')

PL/SQL procedure successfully completed.

SQL> select * from t;

X
--------------
7874051C160D0D

As you can see, the dates are stored in a compact 7-byte format. I’ve added 100 to the century and the year so we can also store negative dates (before 0AD) without any dramas. I’m quite impressed with my ingenuity here. I’m not going to have any of those “number of seconds since 1970” issues, where a 32-bit number might overflow etc etc.

So let us compare that to the DATE datatype.


SQL> create table t1 ( x date );

Table created.

SQL> insert into t1 values ( to_date('20160528211212','yyyymmddhh24miss'));

1 row created.

SQL> select dump(x) from t1;

DUMP(X)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,116,5,28,22,13,13

Let me convert that to plain old bytes so we can compare


SQL> create or replace
  2  function dump_to_hex(p_str varchar2) return varchar2 is
  3    l_str varchar2(100) := p_str;
  4    l_elem varchar2(10);
  5    l_hex varchar2(100);
  6  begin
  7    l_str := substr(l_str,instr(l_str,':')+2);
  8    loop
  9      l_elem := substr(l_str,1,instr(l_str,',')-1);
 10      exit when l_elem is null;
 11      l_hex := l_hex || to_char(to_number(l_elem),'FM0X');
 12      l_str := substr(l_str,instr(l_str,',')+1);
 13    end loop;
 14    return l_hex;
 15  end;
 16  /

Function created.

SQL>
SQL> select dump_to_hex(dump(x)) from t1;

DUMP_TO_HEX(DUMP(X))
-----------------------------------------------------------------
7874051C160D

Oh…. Looks like someone beat me to it Smile

So if you’re thinking about re-inventing your own datatype for dates, perhaps just stick with the one that’s provided for you … it works just fine Smile

Those pesky dates as strings

Connor McDonald , ,

You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”.

But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the good data from the bad data. For example, if your table looks like this:


SQL> select * from T;

DATE_STR
--------------------
qwe
01/01/2000
31/02/2000
12-jan-14
20001212
Jan 14, 2016

6 rows selected.

Some of those values are definitely not dates, some of them definitely are dates, and some of them are might be dates. The only real way of knowing is to try convert those strings to dates, and see what happens. But we cannot just throw a TO_DATE around the column, because the moment we encounter a bad value, our query will crash. Even if all of the data was valid, because the formats of the strings are variable, we’d still get issues with that approach.

And the comes the kicker (because we get this on AskTom all the time)…

“Can we do it without creating a PLSQL function?”

This always bamboozles me…it is like saying “I need to write a book, but I’m only allowed to use a DVORAK keyboard and my tongue, with one eye closed”.

Why restrict yourself on the facilities available ?

Anyway, here is my workaround and not a stored function in sight Smile


SQL> with
  2    function date_checker(p_str varchar2) return date is
  3      l_format sys.odcivarchar2list :=
  4         sys.odcivarchar2list('dd/mm/yyyy','dd-mon-yyyy','yyyymmdd','Mon DD, YYYY');
  5      l_dte date;
  6    begin
  7      for i in 1 .. l_format.count loop
  8        begin
  9          l_dte := to_date(p_str,l_format(i));
 10          return l_dte;
 11        exception
 12          when others then
 13            if i = l_format.count then return null; end if;
 14        end;
 15      end loop;
 16    end;
 17  select date_str, date_checker(date_str) str_as_date
 18  from t
 19  /

DATE_STR             STR_AS_DA
-------------------- ---------
qwe
01/01/2000           01-JAN-00
31/02/2000
12-jan-14            12-JAN-14
20001212             12-DEC-00
Jan 14, 2016         14-JAN-16

6 rows selected.

Gotta love 12c Smile

Is a year a leap year ?

Connor McDonald ,

This post seems timely given that yesterday was Feb 29.

In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic.

This is perhaps one of the very very few exceptions Smile


SQL> set timing off
SQL> create or replace
  2  function is_leap_year1(y number) return boolean is
  3    x date;
  4  begin
  5    x := to_date('2902'||y,'ddmmyyyy');
  6    return true;
  7  exception
  8    when others then return false;
  9  end;
 10  /

Function created.

SQL>
SQL> create or replace
  2  function is_leap_year2(y number) return boolean is
  3  begin
  4    return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 );
  5  end;
  6  /

Function created.

SQL>
SQL> set timing on
SQL> declare
  2    l boolean;
  3  begin
  4   for i in 1 .. 1000000 loop
  5     l := is_leap_year1(1234);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.88
SQL>
SQL> declare
  2    l boolean;
  3  begin
  4   for i in 1 .. 1000000 loop
  5     l := is_leap_year2(1234);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53

Although I must admit, I’m struggling to think of a use case where you would need to check a year for being a leap year hundreds of thousands of times Smile

Automatic date formats

Connor McDonald ,

Notice in all of the examples below that the date string does not match the format mask.

When a date conversion fails, Oracle tries some similar formats to try succeed. This is actually documented as well here

 

Original Format Element Additional Format Elements to Try if Original fails
‘MM’ ‘MON’ and ‘MONTH’
‘MON’ ‘MONTH’
‘MONTH’ ‘MON’
‘YY’ ‘YYYY’
‘RR’ ‘RRRR’

 


SQL> select to_date('01JAN2000','ddmmyyyy') from dual;

TO_DATE('
---------
01-JAN-00

SQL> select to_date('01JAN1999','ddmmyyyy') from dual;

TO_DATE('
---------
01-JAN-99

SQL> select to_date('01JAN2000','dd/mm/yy') from dual;

TO_DATE('
---------
01-JAN-00

SQL>  select to_date('01JAN2000','dd-mm-yyyy') from dual;

TO_DATE('
---------
01-JAN-00

SQL>

Needless to say, relying on any kind of implied format for dates (or any other datatype for that matter) is generally going to get you intro strife eventually Smile

NUMBER data type…what harm can it do ?

Connor McDonald , ,

There’s a somewhat sour discussion going on based attached to the video at https://www.youtube.com/watch?v=jZW-uLb52xk

Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to data types with arbitrary precision, which was stumbled upon by a friend at work.

Let’s start with a simple comparison between two dates. In this case, I’ve just used times, because it still serves to demonstrate the anomaly.

SQL> SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60 delta
  2  FROM   dual;

     DELTA
----------
        50

Thankfully, the database has come back with the right answer of 50 seconds. Of course, we might want to remember that result – so lets store it in a variable:

SQL> variable x number
SQL> begin
  2  SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60
  3  into   :x
  4  FROM   dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
        50

So far so good…let’s now make the tiniest of changes. We’ll use a local PL/SQL variable

SQL> set serverout on
SQL> declare
  2    x number;
  3  begin
  4  SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60
  5  into x
  6  FROM   dual;
  7  dbms_output.put_line(x);
  8  end;
  9  /
50.00000000000000000000000000000000000004

PL/SQL procedure successfully completed.

The use of arbitrary precision for ‘x’ shows how things can go a little bit awry. Similarly, lets look at what happens if convert the dates to simple ‘seconds past midnight’.

SQL> SELECT to_char(TO_DATE('14:03:15','hh24:mi:ss'),'SSSSS') d1,
  2         to_char(TO_DATE('14:02:25','hh24:mi:ss'),'SSSSS') d2
  3  FROM   dual;

D1    D2
----- -----
50595 50545

You might think that the calculation would the same, but when we insert those numbers into the equivalent calculation, we get a slightly different answer

SQL> exec dbms_output.put_line((50595/86400 - 50545/86400)* 24 * 60 * 60 );
49.99999999999999999999999999999999999968

Ultimately, this probably boils down to the fact that the certain division operations can never give a prefect answer in floating point arithmetic. In the example above, 50595/86400 yields the never ending result 0.58559027777777777777777…

Putting PL/SQL aside, there might also be hidden costs if you start using unbounded precision in your database tables. I always remember this example from Steve Adams many years ago.

SQL> create table T ( x1 number, x2 number(6,3));

Table created.

SQL> insert into T values ( 3*(1/3), 3*(1/3) );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T;

        X1         X2
---------- ----------
         1          1

No strange decimals there…but then take look at how you stored that data

SQL> select vsize(x1), vsize(x2) from T;

 VSIZE(X1)  VSIZE(X2)
---------- ----------
        21          2

Bottom line – things can go astray when you dont keep a handle on the appropriate precision to use for your data types.