PHP/Oracle: Time representation

Question

Disclaimer: I'm fully aware that the best way to represent date/times is either Unix timestamps or PHP's DateTime class and Oracle's DATE data type.

With that out of the way, I'm wondering what the most appropriate data types are (in PHP, as well as Oracle) for storing just time data. I'm not interested in storing a date component; only the time.

For example, say I had an employee entity, for which I wanted to store his/her typical work schedule. This employee might work 8:00am - 5:00pm. There are no date components to these times, so what should be used to store them and represent them?

Options I have considered:

1. As strings, with a standard format (likely 24-hour HH:MM:SS+Z).
2. As numbers in the range 0 <= n < 24, with fractional parts representing minutes/seconds (not able to store timezone info?).
3. As PHP DateTime and Oracle DATE with normalized/unused date component, such as 0001-01-01.
4. Same as above, only using Unix timestamps instead (PHP integer and Oracle TIMESTAMP).

Currently I'm using #3 above, but it sort of irks me that it seems like I'm misusing these data types. However, it provides the best usability as far as I can tell. Comparisons and sorts all work as expected in both PHP and Unix, timezone data can be maintained, and there's not really any special manipulation needed for displaying the data.

Am I overlooking anything, or is there a more appropriate way?

Answer 1

If you don't need the date at all, then what you need is the interval day data type. I haven't had the need to actually use that, but the following should work:

interval day(0) to second(6)

Answer 2

The option you use (3) is the best one.

Oracle has the following types for storing times and dates:

• date
• timestamp (with (local) time zone)
• interval year to month
• interval day to second

Interval data types are not an option for you, because you care when to start and when you finish. You could possibly use one date and one interval but this just seems inconsistent to me, as you still have one "incorrect" date.

All the other options you mentioned need more work on your side and probably also lead to decreased performance compared to the native date type.

More information on oracle date types: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1005946

Answer 3

I think that the most correct answer to this question is totally dependant on what you are planning to do with the data. If you are planning to do all your work in PHP and nothing in the database, the best way to store the data will be whatever is easiest for you to get the data in a format that assists you with what you are doing in PHP. That might indeed be storing them as strings. That may sound ghastly to a DBA, but you have to remember that the database is there to serve your application. On the other hand, if you are doing a lot of comparisons in the database with some fancy queries, be sure to store everything in the database in a format that makes your queries the most efficient.

If you are doing tasks like heavy loads calculating hours worked, converting into a decimal format may make things easier for calculations before a final conversion back to hours:minutes. A simple function can be written to convert a decimal to and fro when you are getting data from the database, convert it to decimal, do all your calculations, then run it back through to convert back into a time format.

Using unix timestamps is handy when you are calculating dates, probably not so much when you are calculating times though. While there seem to be some positives using this, such as very easily adding a timestamp to a timestamp, I have found that having to convert everything into timestamps to calculations is pesky and annoying, so I would steer clear of this scenario.

So, to sum up:

1. If you want to easily store, but not manipulate data, strings can be an effective method. They are easy to read and verify. For anything else, choose something else.
2. Calculating as numbers makes for super easy calculations. Convert the time/date to a decimal, do all your heavy hiting, then revert to a real time format and store.
3. Both PHP's Datetime and Oracle's Date are handy, and there are some fantastic functions built into oracle and PHP to manipulate the data, but even the best functions can be more difficult then adding some decimals together. I think that storing the data in the database in a date format is probably a safe idea - especially if you want to do calculations based on the columns within a query. What you plan to do with them inside PHP will determine how you use them.
4. I would rule option four out right off the bat.

Edit: I just had an interesting chat with a friend about time types. Another thing you should be aware of is that sometimes time based objects can cause more problems than they solve. He was looking into an application where we track delivery dates and times. The data was in fact stored in datetime objects, but here is the catch: truck delivery times are set for a particular day and a delivery window. An acceptable delivery is either on time, or up to an hour after the time. This caused some havoc when a truck was to arrive at 11:30pm and turned up 45 minutes later. While still within the acceptable window, it was showing up as being the next day. Another issue was at a distribution center which actually works on a 4:00AM starting 24 hour day. Setting up times worked for the staff - and consolidating it to payments revolving around a normal date proved quite a headache.