
1. Re: seconds to minutes
Frank Kulash Sep 7, 2013 1:58 PM (in response to 0af9d3c8569742b7b1f4c9d4a3652e11)Hi,
There are 60 seconds in every minute, so if s is the number of seconds, then s / 60 is the number of minutes.
This condition:
s / 60 BETWEEN 1
AND 9
will be TRUE if (and only if) s seconds is between 1 and 9 minutes
Note that it will be FALSE if the time (or angle) is the slightest bit over 9 minutes, such as 9:01 (that is, 9 minutes plus 1 second). If you want a condition that is TRUE when the time is at least 1 minute, but less than 10 minutes, you can use:
TRUNC (s / 60) BETWEEN 1
AND 9

2. Re: seconds to minutes
BCV Sep 8, 2013 1:21 PM (in response to 0af9d3c8569742b7b1f4c9d4a3652e11)0af9d3c8569742b7b1f4c9d4a3652e11 wrote:
Hi,
I need to convert seconds to minutes and also determine if the units digit in minutes is from 19. How can I do this?
For example if I have 120 seconds i.e 2 minutes i need to check if 2 is within range 19.
May be Like this,
WITH tab AS (SELECT 1 seconds FROM DUAL
UNION ALL
SELECT 120 seconds FROM DUAL
UNION ALL
SELECT 300 FROM DUAL
UNION ALL
SELECT 540 FROM DUAL
UNION ALL
SELECT 541 FROM DUAL)
SELECT CASE WHEN (seconds / 60) > 9 or (seconds / 60)<1 THEN 'Outta_Range' ELSE 'In_Range' END
Req_Meet
FROM tab;
Cheers!
Message was edited by: BCV

3. Re: seconds to minutes
Ishan Sep 8, 2013 8:25 AM (in response to BCV)BCV,
This would fail in the case if the time evaluates to less than 1 minute. In your example, the first select would result in 0.0166666666666666666666666666666666666667 seconds which according to OP's post should be Out of the Range but the above code would evaluate it as In Range. Between clause should be used instead.
And then as pointed out by Frank, there could be a use of TRUNC option as well.
Ishan

4. Re: seconds to minutes
0af9d3c8569742b7b1f4c9d4a3652e11 Sep 9, 2013 6:16 PM (in response to 0af9d3c8569742b7b1f4c9d4a3652e11)
Hi,I need to check for expample, if it is 35 minutes, 28 minutes etc, units digit of the minute ie whether 5(for 35 mins) is in between 1 and 9 or 8(for 28 mins) is in between 1 and 9.
Thanks.

5. Re: seconds to minutes
Ishan Sep 9, 2013 6:25 PM (in response to 0af9d3c8569742b7b1f4c9d4a3652e11)Basically everything except zero satisfies your range. Is that what you are checking for. If yes, here's the query:
WITH tab AS (SELECT 1 seconds FROM DUAL
UNION ALL
SELECT 120 seconds FROM DUAL
UNION ALL
SELECT 300 FROM DUAL
UNION ALL
SELECT 540 FROM DUAL
UNION ALL
SELECT 541 FROM DUAL
UNION ALL
SELECT 12000 FROM DUAL)
SELECT (seconds / 60) time, CASE WHEN substr((seconds / 60), 1) between 1 and 9 THEN 'Within Range' ELSE 'Out of Range' END
Range_Boundary
FROM tab;
Time Range_Boundary
0.0166666666666666666666666666666666666667 Within Range 2 Within Range 5 Within Range 9 Within Range 9.01666666666666666666666666666666666667 Within Range 200 Out of Range 
6. Re: seconds to minutes
Frank Kulash Sep 9, 2013 6:30 PM (in response to 0af9d3c8569742b7b1f4c9d4a3652e11)Hi,
If n is an integer, then
MOD (n, 10)
is the last digit (theat is, the digit in the units place). N can be any integer, such as the integer produced by TRUNC in my first reply, so the condition
MOD ( TRUNC (s / 60)
, 10
) BETWEEN 1
AND 9
will be TRUE if (and only if) s seconds, expressed in whole minutes (that is, rounded toward 0) ends in 1 through 9.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 11.2.0.2.0).