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;