6 Replies Latest reply: Sep 9, 2013 1:30 PM by Frank Kulash RSS

    seconds to minutes

    0af9d3c8-5697-42b7-b1f4-c9d4a3652e11

      Hi,

       

      I need to convert seconds to minutes and also determine if the units digit in minutes is from 1-9. 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 1-9.

        • 1. Re: seconds to minutes
          Frank Kulash

          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

            0af9d3c8-5697-42b7-b1f4-c9d4a3652e11 wrote:

             

            Hi,

             

            I need to convert seconds to minutes and also determine if the units digit in minutes is from 1-9. 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 1-9.

            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

              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
                0af9d3c8-5697-42b7-b1f4-c9d4a3652e11


                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

                  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.0166666666666666666666666666666666666667Within Range
                  2Within Range
                  5Within Range
                  9Within Range
                  9.01666666666666666666666666666666666667Within Range
                  200Out of Range
                  • 6. Re: seconds to minutes
                    Frank Kulash

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