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
• TIMESTAMP
• INTERVAL

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:

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?