# Help needed with  a Oracle SQL query question.

**URL:** <https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352>\
**Category:** Factual Questions\
**Created:** [April 23, 2007, 7:51pm UTC](https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352 "2007-04-23T19:51:52Z")\
**Posts on this page:** 4\
**Page:** 2

<div class="post-metadata">

**Author:** ![Nanoda](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@Nanoda](https://boards.straightdope.com/u/Nanoda)\
**Post date:** [April 25, 2007, 5:02am UTC](https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352/21 "2007-04-25T05:02:39Z")

</div>

When I had to do something like that (Income /month or something) I used **Noone Special** ’s method.

---

<div class="post-metadata">

**Author:** ![wisernow](https://avatars.discourse-cdn.com/v4/letter/w/13edae/32.png) [@wisernow](https://boards.straightdope.com/u/wisernow)\
**Post date:** [April 25, 2007, 2:43pm UTC](https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352/22 "2007-04-25T14:43:27Z")

</div>

[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.

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [April 25, 2007, 5:31pm UTC](https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352/23 "2007-04-25T17:31:52Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Zyada](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zyada/32/3028_2.png) [@Zyada](https://boards.straightdope.com/u/Zyada)\
**Post date:** [April 25, 2007, 10:33pm UTC](https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352/24 "2007-04-25T22:33:49Z")

</div>

When I want to convert a date to a time period, I usually do date_24_6 (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’

[Previous page](https://boards.straightdope.com/t/help-needed-with-a-oracle-sql-query-question/401352.md?page=1)