Datatypes for DATES

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> 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> exec store_date('20160528211212')

PL/SQL procedure successfully completed.

SQL> select * from t;


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;

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> select dump_to_hex(dump(x)) from t1;


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

4 thoughts on “Datatypes for DATES

  1. Never, ever, underestimate the power of a modern duhveloper to turn “really easy” into a nightmare for those charged with maintaining and optimizing the data storage and retrieval of an application.
    Ah yes, it’s called “kewl coding”. Or something…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s