When I had to do something like that (Income /month or something) I used Noone Special’s method.
[QUOTE=Punoqllads]
It’s like converting from feet to miles.
[/QUOTE]
Coneverting feet to miles, or from any one unit to another requires a simple multiplication with the conversion factor. In other words we know that the equivalent of say one inch is 2.4 centimeters. Threfore x inches is x times 2.4 cms. But isn’t this case a little different. What does mutilpying the number of intervals with time differences give us and how?
[QUOTE=Punoqllads]
There are 6 10-minute intervals per hour, and there are 24 hours per day, so converting from days to intervals requires multiplying the difference in days by 144. The floor() function then rounds the value down to the next integer value, e.g., 9 minutes 59 seconds after the initial time will round down to the 0th interval.
[/QUOTE]
I got the part where you calculated the 144 ten minute intervals in 24 hours. I also get the floor function rounding off to the lowest integer. What I did not get is the multiplication of the 144 with the time difference between start time and login time yielding results. I would really appreciate it if you could elaborate on this part and help me understand the logic.
Oracle calls them “dates” but they’re really date+time. The difference between the two dates is a floating-point number representing the number of days between them, including a fractional part. By multiplying this number by 144 we get the number of 10-minute intervals between the two dates. Does that answer your question?
When I want to convert a date to a time period, I usually do date246 (for instance), i.e. 24 hours in a day, 6 ten minute periods in an hour. Makes it a bit easier to change.
I’d probably do this similar to no one special :
select substr(to_char(login_time, ‘mm/dd/yy hh24:mi’), 1, 13)||‘0’, count(*)
from user_login_record
group by substr(to_char(login_time, ‘mm/dd/yy hh24:mi’), 1, 13)||‘0’