Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql query to find correct time diffrence
"If I wanted to go there I wouldn't start from here"
I.e. Why are you storing times in a VARCHAR2 field?
I.e. You may have a bad table design, ask youself what you are storing
this data for? Separate the data storage from data presentation to
users, they are two separate issues.
Either, if you want to store an absolute time and date, store the data
as type DATE.
Then arithmetic is easy, if you subtract two DATE values you get a
difference in decimal days. Multiply by 1440 for minutes, or 86400 for
seconds. No problems with rollover between hours or between days, or
+/- values.
Or, if you really need to store time of day only, without any date,
store it as an integer "Minutes past midnight", 0 to 1439.
Arithmetic is then equally easy.
How do you display "Minutes past midnight" to the user:
TO_CHAR(TRUNC(SYSDATE)+{value}/1440,'HH24:MI')
or whatever format your user likes.
Received on Thu Dec 29 2005 - 02:43:43 CST