SQL & PL/SQL

Hello,

I am wondering about the best way to implement a time without date column. Here is the requirement : the time without date column must be combined with a date without time column in order to generate a date with time.

In the example below, I have a catalog of course modules with a specified starting time (09:00:00). I then create instances of one of those courses at a specified date (e.g. 2012/01/01). I must then show the user the composite datetime as 2012/01/01 09:00:00, and possibly check constraints on that value.

The date without time is not a problem, a date column with TRUNC(date)=date seems to do the job. For the time without date, I have considered two options :

1) A date column with fixed date : TRUNC(date) = '2001/01/01' (or whatever).
2) A number column between 0 and 86400 with the number of seconds since the beginning of the day.

What would you do ? Or is there a third, better way ?

CREATE TABLE TEST.MODULE_1
(
  MODULE_ID     NUMBER(10),
  MODULE_NAME   VARCHAR2(30 BYTE)               NOT NULL,
  MODULE_START  DATE                            NOT NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE TEST.MODULE_2
(
  MODULE_ID     NUMBER(10),
  MODULE_NAME   VARCHAR2(30 BYTE)               NOT NULL,
  MODULE_START  NUMBER(6)                       NOT NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE TEST.COURSE
(
  COURSE_ID    NUMBER(10),
  MODULE_ID    NUMBER(10)                       NOT NULL,
  COURSE_DATE  DATE                             NOT NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE TEST.MODULE_1 ADD (
  CONSTRAINT CK_MODULE_START_1
 CHECK (TRUNC(module_start) = TO_DATE('2001/01/01','yyyy/mm/dd')),
  CONSTRAINT MODULE_PK
 PRIMARY KEY
 (MODULE_ID));

ALTER TABLE TEST.MODULE_2 ADD (
  CONSTRAINT CK_MODULE_START_2
 CHECK (module_start BETWEEN 0 AND 86399),
  PRIMARY KEY
 (MODULE_ID));

ALTER TABLE TEST.COURSE ADD (
  CONSTRAINT CK_COURSE_DATE
 CHECK (TRUNC(course_date) = course_date),
  CONSTRAINT COURSE_PK
 PRIMARY KEY
 (COURSE_ID));


SET DEFINE OFF;
Insert into TEST.COURSE
   (COURSE_ID, MODULE_ID, COURSE_DATE)
 Values
   (1, 1, TO_DATE('01/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST.COURSE
   (COURSE_ID, MODULE_ID, COURSE_DATE)
 Values
   (2, 1, TO_DATE('01/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

SET DEFINE OFF;
Insert into TEST.MODULE_1
   (MODULE_ID, MODULE_NAME, MODULE_START)
 Values
   (1, 'Oracle native datatypes', TO_DATE('01/01/2001 09:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

SET DEFINE OFF;
Insert into TEST.MODULE_2
   (MODULE_ID, MODULE_NAME, MODULE_START)
 Values
   (1, 'Oracle native datatypes', 32400);
COMMIT;