Storing the time in Oracle

Let me quickly explain. I have an application to manage visitors.

We have a start date of the visit (a visit can only be for one day). We can add a time for example t开发者_运维问答he time of a tour, the time the group will be having lunch, the time the group will be having a presentation etc. All times will take the start date of the visit and then append the time accordingly.

Simple code:

   // set tour time in tour table
    $y->setTourTime($visit->getVisitDate("Y-m-d") . $tourTime);
    // can have many presentation
    $p->setPresentationTime($visit->getVisitDate("Y-m-d") . $tourTime);

So I have many time stamps across multiple tables. The problem I have however, if I decide to change the start date in the visit entity, then I'll have to change all of the timestamps across the related tables (tour guide, lectures etc.) This is ugly

What I would prefer is to have a visit date, such as 2010-10-10 in the visit table. And then in the lecture tables, guide tables etc. to just store the time and not the date. How would you do that? just store it as a string, i.e. "10:00"?

Thanks for any input. :-)

You have several possibilities. I prefer to have datatypes that are best suited for the job. Calculating with times is best done using DATE, TIMESTAMP and INTERVAL, so I'd use something like this:

SQL> create table visits
  2  ( startdate              date
  3  , starttime_tour         interval day(0) to second(0)
  4  , starttime_lunch        interval day(0) to second(0)
  5  , starttime_presentation interval day(0) to second(0)
  6  , constraint visits_ck1 check (startdate = trunc(startdate))
  7  )
  8  /

Table created.

You can read more about the INTERVAL DAY TO SECOND datatype here: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#SQLRF00207

And here is an example how you insert and select from it:

SQL> insert into visits
  2  values
  3  ( trunc(sysdate)
  4  , to_dsinterval('0 09:00:00')
  5  , to_dsinterval('0 12:00:00')
  6  , to_dsinterval('0 13:00:00')
  7  )
  8  /

1 row created.

SQL> select startdate
  2       , starttime_tour
  3       , starttime_lunch
  4       , starttime_presentation
  5    from visits
  6  /

STARTDATE           STARTTIME_TOUR       STARTTIME_LUNCH      STARTTIME_PRESENTATI
------------------- -------------------- -------------------- --------------------
17-12-2010 00:00:00 +0 09:00:00          +0 12:00:00          +0 13:00:00

1 row selected.

And calculating is very easy now:

SQL> select startdate
  2       , startdate + starttime_tour as tour
  3       , startdate + starttime_lunch as lunch
  4       , startdate + starttime_presentation as presentation
  5    from visits
  6  /

STARTDATE           TOUR                LUNCH               PRESENTATION
------------------- ------------------- ------------------- -------------------
17-12-2010 00:00:00 17-12-2010 09:00:00 17-12-2010 12:00:00 17-12-2010 13:00:00

1 row selected.

Hope this helps.

Regards, Rob.

If you want to manipulate as a time it might be better to just create two date fields. One holds the visit date, the other holds the tour time with a generic date attached (1/1/2000 or something consistent & non-specific) - you ignore the date portion of the tour time but can still do time-based comparisons if necessary without converting a string to a time value.