Jun 242013
create table roll_your_own_time_types (
  where_everything_is_a varchar2(5) 

alter table roll_your_own_time_types 
add constraint so_we_are_sure_it_is_a_time 
check (regexp_like(

alter table roll_your_own_time_types add (
    interval day(0) to second

In addition to saving you from having to write validaiton code, using an interval implements
all the arthimetic operations for you as well, so you can easily find the difference between
two times. This kind of datatype crime is more common with dates, but I suspect that's
because it's rare times are stored with no date component.

As usual, there’s an oracle-base article detailing how to use interval datatypes if you want a more thorough explanation.

  2 Responses to “It’s time you found a better datatype”

  1. we could also …

    sokrates@11.2 > insert into roll_your_own_time_types ( where_everything_is_a ) values('29:38');

    1 row created.

    sokrates@11.2 > REM argrrr ! DOESN'T look like a valid TIME !!!
    sokrates@11.2 > REM
    sokrates@11.2 > REM enable some undocumented ...
    sokrates@11.2 > alter session set events '10407 trace name context forever, level 1';

    Session altered.

    sokrates@11.2 > alter table roll_your_own_time_types add ( we_can_use_the_undocumented time );

    Table altered.

    sokrates@11.2 > insert into roll_your_own_time_types ( we_can_use_the_undocumented ) values(time '29:38:00');
    insert into roll_your_own_time_types ( we_can_use_the_undocumented ) values(time '29:38:00')
    ERROR at line 1:
    ORA-01850: hour must be between 0 and 23

    sokrates@11.2 > insert into roll_your_own_time_types ( we_can_use_the_undocumented ) values(time '23:38:00');

    1 row created.

    , but we won’t do, correct ? ( undocumented = evil )


    • I realised after posting that I missed the validation to check that hours can’t be greater than 23, just goes to show the dangers of manual checks.

      Thanks for the info about time types – I wasn’t aware of that (probably related to it being undocumented…). Perhaps Oracle will make it available in a future version (12c?)

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>