Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Unanswered: TIME data type? is there such thing

    Pardon my ignorance in this matter but I was wondering if there is a data type
    that only stores the current time into the database? I know that there is a DATE data type but from what i hear, that stores both the date and the time?

    any comments will be welcome


  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    No, Oracle does not support the TIME datatype.

    You will either need to use a DATE and ignore the "date" part (which is very ugly from my point of view) or us an integer value to store "seconds after midnight". Or you could use three columns: hour, minute, second.

    I would not use a varchar field because that will make comparisons (> some_time) very complicated and error prone

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    i would use date or timestamp and only display what you want to display.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Or do what oracle does in their applications. use a fixed date and then store the time element.
    to_date('31-dec-4012','dd-mon-yyyy') + (sysdate - trunc(sysdate))
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I've just seen in a posting in the Oracles newsgroup the suggestion to use the datatype "INTERVAL DAY TO SECOND" to store this kind of information.

    This sounds like an interesting workaround:
    Code:
    CREATE TABLE time_info 
    (
      id           INTEGER,
      event_time   INTERVAL DAY TO SECOND
    );
    
    INSERT INTO time_info (id, event_time) 
    VALUES 
    (1, INTERVAL '14:20' HOUR TO MINUTE);

  6. #6
    Join Date
    Nov 2008
    Posts
    5
    Quote Originally Posted by shammat
    I've just seen in a posting in the Oracles newsgroup the suggestion to use the datatype "INTERVAL DAY TO SECOND" to store this kind of information.
    The problem with Oracle intervals is the lack of support from various DB API and frameworks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •