What's the right way to store time intervals in a database ? I'm currently using a DATETIME field to store the start time of a user session, and a second DATETIME field to store the duration. The thing is I'm having some problems formatting the data correctly from a JAVA application, so I have to perform some nasty operations before presenting the dates.
This made me think that maybe it would be a better idea to store the duration as an INTEGER (number of seconds), and then convert it to hh:mm:ss in the app or in the query (this field can't be greater than 18 hours).
So, what do you think is the best way to store the session duration ?
If you are on 9i, there is the INTERVAL DAY TO SECOND data type. Otherwise, the best way to store a duration is as a NUMBER, e.g. as an INTEGER number of seconds. You can add the duration to the start time to get the end time, but you need to convert from seconds to days first: