This discussion is archived
6 Replies Latest reply: May 24, 2012 2:41 PM by Paulie RSS

A real puzzler...

Paulie Pro
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    >

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


    Paul...
  • 3. Re: A real puzzler...
    Tubby Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    >

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points