Storing Time Information In Oracle

I recently had a requirement to store a time within Oracle, not a timestamp or a date – just the time. As it turns out, storing just the time information in Oracle isn’t something you can do without a little bit of work.

Oracle provides you a few different data types for handling date and time:

  • DATE

Unfortunately, none of the supplied data types are an exact match for a time such as 10:30 AM. The good news is that both a DATE and a TIMESTAMP data types contain time information – so it is possible to do what you want with a little slight of hand.

The slight of hand I mentioned has to do with the default behaviour of Oracle when inserting only a time component of a DATE or a TIMESTAMP data type. For the rest of this discussion, lets assume you have a simple table set up as follows:

  1. CREATE TABLE Times (
  2. id INT NOT NULL,
  3. thedate DATE NOT NULL,
  4. thetimestamp TIMESTAMP NOT NULL
  5. );

With the following three rows of data:

  1. INSERT INTO Times (id, thedate, thetimestamp) VALUES (1, SYSDATE, SYSDATE);
  2. INSERT INTO Times (id, thedate, thetimestamp) VALUES (2, TO_DATE('10:30 AM', 'HH:MI AM'), TO_DATE('10:30 AM', 'HH:MI AM'));
  3. INSERT INTO Times (id, thedate, thetimestamp) VALUES (3, TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'), TO_DATE('0001-01-01 10:30 AM', 'YYYY-MM-DD HH:MI AM'));

When selecting that grid of information out, you’ll receive:

id thedate thetimestamp
1 28/03/2007 11:32:25 PM 28/03/2007 11:32:25.000000 PM
2 1/03/2007 10:30:00 AM 1/03/2007 10:30:00.000000 AM
3 1/01/0001 10:30:00 AM 1/01/0001 10:30:00.000000 AM

There are more than one solution to this problem, as if you’re using any of the standard date/time data types – you can always store the time component. What may or may not be of interest to everyone is what Oracle does with the date component of a DATE or TIMESTAMP data type when you don’t provide the standard date components of a date/time data type.

If you take notice of row #1 returned, you’ll see that because the inserted values for thedate and thetimetamp was SYSDATE, it has stored the date component as you’d expect.

Compare that against row #2 and you’ll notice that the insert statement simply provided the time component and made no mention of the date. When Oracle returned that time value out, it has automatically substituted the first day of the current month into the date component.

When inserting row #3, an arbitrary date of 1 Jan 0001 was supplied. When reading that information back out of Oracle, it handles the date component in a similar fashion to how it was handled for row #1 using the SYSDATE; ie you get the expected result back.

Since you can return any number of rows from Oracle doing standard date/time arithmetic, it really comes down to preference. What option above has the most semantic meaning to a user or the data in the database when you’re required to store a time and not a timestamp?

For consistency reasons, my personal preference leans towards applying technique #3 and here are a couple of simple reasons why:

  • When dealing with a DATE or TIMESTAMP value stored like that in PL/SQL, you’ll always know what value is stored in the date component of the value.
  • If you’re handling this information in an application language such as Microsoft .NET, Java or Python – you will always know what value to expect in the date component. Using technique #1, the date component will change for every row. Using #2, the date component of the value will change per row depending on what month the row was inserted

Does anyone else have a hot tip for storing a straight time value in Oracle?

4 thoughts on “Storing Time Information In Oracle

  1. I know! Varchar!!! “10:30 AM”, or perhaps you could even go “Ten thirty aye em!”

    Or was that the line of code you were trying to *replace*? :-p

  2. Liam,

    I guess there is nothing wrong with using a VARCHAR to store the time, so long as you have something to make sure it is always being written in a format that you can conveniently cast when it hits your application.

    I personally prefer using some sort of a date based type, simply because it’ll mean that you’ll never be able to store a non-date compliant value into the field – which you may be able to do using a VARCHAR type without enough validation on it.


  3. I am new at Oracle and I was assigned on a project where I insert dates and time. I am having a problem here, can anyone point out whats wrong with this to_date entry:

    to_date(’14/05/2007 05:00:00 PM’,’DD/MM/YYYY HH12:MI:SS PM’)

    I have also tried:

    to_date(’14/05/2007 05:00:00 PM’,’DD/MM/YYYY HH12:MI:SS AM’)

    gives me this same error:

    “ORA-01855: AM/A.M. or PM/P.M. required”


    By the way i tried using this as well:

    to_date(’14/05/2007 05:00:00’,’DD/MM/YYYY HH12:MI:SS PM’)

    the entry was accepted but the problem is it is AM not PM.

    Thank you very much, an answer will be very much appreciated.

  4. select to_char(sysdate,’HH24:MM:SS’) as “current time” from dual;
    select to_char(sysdate,’HH24:MM:SS’) as “time now” from dual;

Comments are closed.