6 Replies Latest reply: May 24, 2012 4:41 PM by Paulie RSS

    A real puzzler...

    Paulie
      Hi all,

      In response to another thread, i worked on a problem and I came across a real puzzler.

      The table and insert statements are at the end of the post. What I want is if the seconds
      are greater than 29, to round up to the next minute, if below 29, round down to the
      minute - i.e. with 00 seconds - i.e. the seconds are not displayed in the time- just
      the minute as newly calculated - if it has changed.

      The result of the query below is here - the first field is the date with seconds - the
      second is the changed one - if indeed it changes.
      2012/MAY/15 14:56:00 2012/MAY/15 14:56                                                                                                                                                                                                                          
      2012/MAY/15 14:57:00 2012/MAY/15 14:57                                                                                                                                                                                                                          
      2012/MAY/15 15:00:40 2012/MAY/15 15: 01                                                                                                                                                                                                                         
      2012/MAY/15 15:00:40 2012/MAY/15 15: 01                                                                                                                                                                                                                         
      2012/MAY/15 15:00:45 2012/MAY/15 15: 01 
      The really interesting thing is how there's an extra space between the : and the minutes in
      the case of the seconds being above 29. The query is here
      select to_char(starttime, 'YYYY/MON/DD HH24:MI:SS') || ' ' ||  -- this line is a control - I'm able to see the original dates.
        case when to_number(to_char(starttime, 'SS')) <= 29 then to_char(starttime, 'YYYY/MON/DD HH24:MI') 
                when to_number(to_char(starttime, 'SS')) > 29   then to_char(starttime, 'YYYY/MON/DD HH24:')
            || to_char(to_number((to_char(starttime,'MI')) + 1), '09')
        end
      from trans
      The table definiton and insert statements are below my signature. I'm really baffled by this,
      any help appreciated.


      Paul...

      CREATE TABLE TRANS
      (
        CALLTYPE     NVARCHAR2(6),
        ORIGANI                 NVARCHAR2(40),
        TERMANI                 NVARCHAR2(40),
        STARTTIME               DATE,
        STOPTIME                DATE,
        CELLID                  NVARCHAR2(10),
        CONNECTSECONDS          NUMBER,
        SWITCHCALLCHARGE        NUMBER
      )
      / 
      
       
      INSERT INTO TRANS VALUES ('IN','555988888','222772088',to_date('05/15/2012 11:05:05','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 11:05:39','mm/dd/yyyy hh24:mi:ss'),null,34,18000)
      / 
      INSERT INTO TRANS VALUES ('MSG','555988888','222772088',to_date('05/15/2012 11:05:04','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 11:05:38','mm/dd/yyyy hh24:mi:ss'),'2793',33,null)
      / 
      INSERT INTO TRANS VALUES ('IN','555000010','222772081',to_date('05/15/2012 07:38:53','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 07:40:01','mm/dd/yyyy hh24:mi:ss'),null,8,18000)
      / 
      INSERT INTO TRANS VALUES ('MSG','555000010','222772081',to_date('05/15/2012 07:38:51','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 07:38:09','mm/dd/yyyy hh24:mi:ss'),'27d1',27,null)
      / 
      INSERT INTO TRANS VALUES ('IN','555000010','222772081',to_date('05/15/2012 07:59:40','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 08:01:00','mm/dd/yyyy hh24:mi:ss'),null,80,36000)
      / 
      INSERT INTO TRANS VALUES ('MSG','555000010','222772081',to_date('05/15/2012 08:00:01','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 08:01:20','mm/dd/yyyy hh24:mi:ss'),'27d2',80,null)
      / 
      INSERT INTO TRANS VALUES ('IN','555988889','222772088',to_date('05/15/2012 14:54:12','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 14:54:59','mm/dd/yyyy hh24:mi:ss'),null,47,18000)
      / 
      INSERT INTO TRANS VALUES ('IN','555988889','222772088',to_date('05/15/2012 14:56:00','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 14:56:30','mm/dd/yyyy hh24:mi:ss'),null,30,18000)
      / 
      INSERT INTO TRANS VALUES ('MSG','555988889','222772088',to_date('05/15/2012 14:57:00','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 14:58:32','mm/dd/yyyy hh24:mi:ss'),'27cf',32,null)
      / 
      INSERT INTO TRANS VALUES ('IN','555988888','222772000',to_date('05/15/2012 15:00:40','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 15:01:40','mm/dd/yyyy hh24:mi:ss'),null,60,0)
      / 
      INSERT INTO TRANS VALUES ('IN','555988888','222772000',to_date('05/15/2012 15:00:40','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 15:01:40','mm/dd/yyyy hh24:mi:ss'),null,60,36000)
      / 
      INSERT INTO TRANS VALUES ('MSG','555988888','222772000',to_date('05/15/2012 15:00:45','mm/dd/yyyy hh24:mi:ss'),to_date('05/15/2012 15:01:45','mm/dd/yyyy hh24:mi:ss'),'27cf',60,null)
      / 
       
      commit
      / 
        • 1. Re: A real puzzler...
          Tubby
          Paulie wrote:
          Hi all,

          In response to another thread, i worked on a problem and I came across a real puzzler.

          The table and insert statements are at the end of the post. What I want is if the seconds
          are greater than 29, to round up to the next minute, if below 29, round down to the
          minute - i.e. with 00 seconds - i.e. the seconds are not displayed in the time- just
          the minute as newly calculated - if it has changed.
          The Round function does this.
          ME_XE?select round(to_date('01-jan-2012 14:30:29', 'dd-mon-yyyy hh24 MI:SS'), 'MI') from dual;
          
          ROUND(TO_DATE('01-JAN-2012
          --------------------------
          01-JAN-2012 02 30:00
          
          1 row selected.
          
          Elapsed: 00:00:00.01
          ME_XE?select round(to_date('01-jan-2012 14:30:30', 'dd-mon-yyyy hh24 MI:SS'), 'MI') from dual;
          
          ROUND(TO_DATE('01-JAN-2012
          --------------------------
          01-JAN-2012 02 31:00
          
          1 row selected.
          
          Elapsed: 00:00:00.00
          ME_XE?
          However in answer to your question ... you would need to use the FM modifier to avoid the extra space you're seeing.
          || to_char(to_number((to_char(starttime,'MI')) + 1), 'fm09')
          http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#SQLRF00216

          Cheers,

          Edited by: Tubby on May 24, 2012 2:33 PM

          Added supporting link to documentation.
          • 2. Re: A real puzzler...
            Paulie
            >

            The Round function does this.
            I haven't used the ROUND function. Thanks anyway - any other ideas?


            Paul...
            • 3. Re: A real puzzler...
              Tubby
              Paulie wrote:
              >

              The Round function does this.
              I haven't used the ROUND function. Thanks anyway - any other ideas?


              Paul...
              I amended my post to answer your question verbatim. Please check that.

              My point was that the round function would be a much cleaner way of achieving the result you desire.

              Cheers,
              • 4. Re: A real puzzler...
                Paulie
                Tubby wrote:
                Paulie wrote:
                >

                The Round function does this.
                I haven't used the ROUND function. Thanks anyway - any other ideas?


                Paul...
                I amended my post to answer your question verbatim. Please check that.
                Great - thanks for that.
                My point was that the round function would be a much cleaner way of achieving the result you desire.
                Yes, I've looked at the code - I thought you meant that the effect I described was a result
                of using the ROUND function - I was a bit quick on the keyboard fingers.

                Cheers,
                And to you - Paul...
                • 5. Re: A real puzzler...
                  Frank Kulash
                  Hi, Paul,

                  As Tubby said, ROUND does just what you want.

                  In case you're wondering where the extra space comes from, the default behavior of TO_CHAR (n, str), where n is a NUMBER, is to leave space for a minus sign at the very beginning of the output string, so
                  TO_CHAR (n, '09')
                  returns 3 characters, not 2. To change whether or not that kind of padding appears in the output, you can use 'FM' in the format string, like this:
                  ...                   || to_char ( to_number ( ( to_char (starttime,'MI')
                                                          )
                                            + 1
                                            )
                                      , 'FM09'
                                      )
                  That still leaves the problem, that, when the original time is, for example, 11:59:35, you're returning '11:60:00', when you should be returning '12:00:00'.

                  Oracle provides many handy DATE manipulation functions, like ROUND, that take into account things like '00' being the minute right after '59'. Whenever you're tempted to convert a DATE to a string (or to a NUMBER) just so you can manipulate it, see if there's already a function that you can use.
                  • 6. Re: A real puzzler...
                    Paulie
                    >

                    Hi Frank,
                    As Tubby said, ROUND does just what you want.
                    <Hides head in shame> Yes, I know that now.


                    That still leaves the problem, that, when the original time is, for example, 11:59:35, you're returning '11:60:00', when you should be returning '12:00:00'.

                    Oracle provides many handy DATE manipulation functions, like ROUND, that take into account things like '00' being the minute right after '59'. Whenever you're
                    tempted to convert a DATE to a string (or to a NUMBER) just so you can manipulate it, see if there's already a function that you can use.
                    More research for me tomorrow ;)

                    Thanks for your input.


                    Paul...