1 2 Previous Next 18 Replies Latest reply: Feb 26, 2013 8:02 AM by user522961 RSS

    between ( 9 PM) and (7 AM)

    user522961
      Hi,
      on 11g R2

      Can you please give me the right syntax for the follwing :
      select prcstype,prcsname,prcsinstance,runcntlid, dbname,oprid,runstatus,begindttm, enddttm,
            to_number(to_char(enddttm,'DDMMYYHH24MMSS') )- to_number(to_char(begindttm,'DDMMYYHH24MMSS'))duration
             from psprcsrqst where oprid='user1' and begindttm between ( 9 PM) and (7 AM);
      More over duration would it be in minutes, or in second ? And how can I have the total of duration ?

      Thanks and regards.
        • 1. Re: between ( 9 PM) and (7 AM)
          rp0428
          >
          Can you please give me the right syntax for the follwing :
          >
          Can you explain, in English not code, what it is you are wanting to do? And what the datatypes are of the data you are trying to do it with?
          • 2. Re: between ( 9 PM) and (7 AM)
            sb92075
            Handle:     user522961
            Status Level:     Newbie
            Registered:     Aug 1, 2006
            Total Posts:     4,407

            WOW!
            after thousands of posts & after almost 7 years, why does it appear that you'd be challenged to spell S-Q-L?

            post CREATE TABLE psprcsrqst statement
            • 3. Re: between ( 9 PM) and (7 AM)
              Frank Kulash
              Hi,

              If begindttm is a DATE, you can find rows where the time component is between 9 PM (which is 21 in HH24 notation) and 7 AM like this:
              WHERE   TO_CHAR (begindttm, 'HH24:MI:SS') 
                       NOT BETWEEN '07:00:00'
                        AND     '21:00:00'
               

              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.
              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).
              See the forum FAQ {message:id=9360002}
              • 4. Re: between ( 9 PM) and (7 AM)
                Etbin
                Maybe
                select prcstype,prcsname,prcsinstance,runcntlid,dbname,oprid,runstatus,begindttm,enddttm,
                       enddttm - begindttm duration
                  from psprcsrqst
                 where oprid = 'user1'
                   and begindttm between trunc(begindttm) + 21 / 24 and trunc(begindttm) + 31 / 24
                Regards

                Etbin

                Edited by: Etbin on 19.2.2013 18:35
                Oops ! Should have checked before posting

                Edited by: Etbin on 19.2.2013 18:37
                Seems I have misunderstood too :(
                • 5. Re: between ( 9 PM) and (7 AM)
                  stefan nebesnak
                  user522961 wrote:
                  to_number(to_char(enddttm,'DD<font style="background-color: #F5A9A9">MM</font>YYHH24<font style="background-color: #F5A9A9">MM</font>SS') )- t
                  Generally, the difference between dates (DATE_1 - DATE_2) is in days.

                  <tt>
                  with t as
                  (select(
                  to_date('01.05.2013 19:30:00','DD.<font style="background-color: #F5A9A9">MM</font>.YYYY HH24:<font style="background-color: #F5A9A9">MI</font>:SS')
                  -
                  to_date('01.05.2013 17:00:00','DD.<font style="background-color: #F5A9A9">MM</font>.YYYY HH24:<font style="background-color: #F5A9A9">MI</font>:SS')
                  ) days
                  from dual
                  )
                  select t.days<font style="background-color: #FFFFCC">*1</font> days, t.days<font style="background-color: #FFFFCC">*24</font> hours, t.days<font style="background-color: #FFFFCC">*24*60</font> minutes, t.days<font style="background-color: #FFFFCC">*24*60*60</font> seconds <font color="#FF0000">/*etc.*/</font> from t;
                  </tt>
                  . DAYS                 HOURS  MINUTES SECONDS
                  1 0,104166666666667    2,5    150     9000
                  • 6. Re: between ( 9 PM) and (7 AM)
                    EdStevens
                    user522961 wrote:
                    Hi,
                    on 11g R2

                    Can you please give me the right syntax for the follwing :
                    select prcstype,prcsname,prcsinstance,runcntlid, dbname,oprid,runstatus,begindttm, enddttm,
                    to_number(to_char(enddttm,'DDMMYYHH24MMSS') )- to_number(to_char(begindttm,'DDMMYYHH24MMSS'))duration
                    from psprcsrqst where oprid='user1' and begindttm between ( 9 PM) and (7 AM);
                    More over duration would it be in minutes, or in second ? And how can I have the total of duration ?

                    Thanks and regards.
                    For your own sanity, you should learn to format you code - even if it is just pseudo-code.
                    For the sanity of those from whom you expect assistance you should preserve that formatting with the \
                     tags.
                    Like this:
                    select prcstype,
                    prcsname,
                    prcsinstance,
                    runcntlid,
                    dbname,
                    oprid,
                    runstatus,
                    begindttm,
                    enddttm,
                    to_number(to_char(enddttm,'DDMMYYHH24MMSS') ) - to_number(to_char(begindttm,'DDMMYYHH24MMSS'))duration
                    from psprcsrqst
                    where oprid='user1' and
                    begindttm between ( 9 PM) and (7 AM);
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                    • 7. Re: between ( 9 PM) and (7 AM)
                      user522961
                      Thanks to all.

                      I ran what you suggested.
                      The first line is out of range (19/02/13 06:55:46,040000000 ). it is from the last morning. And I want from last evening 9 Pm til this morning 7 (AM).
                      And also some negative duration ??
                      How can I have the total of duration ?
                      Here is what I have:
                      select prcstype,prcsname,prcsinstance,runcntlid, dbname,oprid,runstatus,begindttm, enddttm,
                            to_number(to_char(enddttm,'DDMMYYHH24MMSS') )- to_number(to_char(begindttm,'DDMMYYHH24MMSS'))duration
                             from psprcsrqst where oprid='USER1'
                             AND BEGINDTTM > sysdate - 1
                             AND   TO_CHAR (begindttm, 'HH24:MI:SS')   NOT BETWEEN '07:00:00' AND     '21:00:00';
                      
                      
                      PRCSTYPE                       PRCSNAME     PRCSINSTANCE                            RUNCNTLID                      DBNAME   OPRID                          RUNSTATUS BEGINDTTM                 ENDDTTM                   DURATION                                
                      ------------------------------ ------------ --------------------------------------- ------------------------------ -------- ------------------------------ --------- ------------------------- ------------------------- --------------------------------------- 
                      Application Engine             LOCK_I_AE    66118                                   UNLOCKUSER                     MYDB   USER1                        9         19/02/13 06:55:46,040000000 19/02/13 06:56:31,010000000 -15                                     
                      Application Engine             LOCK_I_AE    66168                                   LOCKUSER                       MYDB   USER1                        9         19/02/13 21:01:00,041000000 19/02/13 21:01:15,495000000 15                                      
                      Application Engine             SCRTY_CLSUPD 66173                                   SCRTY_CLSUPD                   MYDB   USER1                        9         19/02/13 21:41:49,615000000 19/02/13 21:42:04,649000000 -45                                     
                      Application Engine             SCRTY_SJTUPD 66177                                   SCRTY_SJTUPD                   MYDB   USER1                        9         19/02/13 21:42:21,103000000 19/02/13 21:42:51,229000000 30                                      
                      Application Engine             SCRTY_OPRCLS 66182                                   SCRTY_OPRCLS                   MYDB   USER1                        9         19/02/13 21:43:07,629000000 19/02/13 21:43:22,692000000 15                                      
                      Application Engine             INTFPS_FOE_I 66186                                   INTFPS_FOE_I                   MYDB   USER1                        9         19/02/13 21:52:41,407000000 19/02/13 21:55:45,377000000 4                                       
                      Application Engine             HRS_JSCH_IDX 66202                                   HRS_JSCH_IDX                   MYDB   USER1                        9         19/02/13 22:00:19,377000000 19/02/13 22:03:43,435000000 24                                      
                      Application Engine             HRS_SRCH_IDX 66218                                   HRS_SRCH_IDX                   MYDB   USER1                        9         19/02/13 22:03:59,926000000 19/02/13 23:40:49,307000000 9990                                    
                      Application Engine             HRS_SRCH_APP 66220                                   e24b52677ad711e286fc           MYDB   USER1                        9         19/02/13 22:04:16,505000000 19/02/13 23:40:18,210000000 10002                                   
                      Application Engine             HRS_AM       66627                                   HRS_AM                         MYDB   USER1                        9         19/02/13 23:41:06,386000000 19/02/13 23:41:21,511000000 15                                      
                      Application Engine             HRS_JOB_AGNT 66631                                   HRS_JOB_AGNT                   MYDB   USER1                        9         19/02/13 23:41:38,140000000 19/02/13 23:41:53,276000000 15                                      
                      Application Engine             HR_PERSDATA  66640                                   HR_PERSDATA                    MYDB   USER1                        9         20/02/13 00:30:58,822000000 20/02/13 00:31:14,197000000 -44                                     
                      Application Engine             SCRTY_SJTDLY 66636                                   SCRTY_SJTDLY                   MYDB   USER1                        9         20/02/13 00:05:17,675000000 20/02/13 00:05:32,863000000 15                                      
                      Application Engine             TRANS_XML_I  66645                                   TRANS_XML_I                    MYDB   USER1                        9         20/02/13 00:31:31,198000000 20/02/13 00:31:45,656000000 14                                      
                      
                      14 rows selected
                      Thanks and regards.
                      • 8. Re: between ( 9 PM) and (7 AM)
                        jeneesh
                        user522961 wrote:
                        Thanks to all.

                        I ran what you suggested.
                        The first line is out of range . it is from thelast morning. And I want from last evening 9 Pm til this morning 7 (AM).
                        Is this not simply like
                        AND BEGINDTTM between trunc(sysdate - 1)+(21/24)
                                      and trunc(sysdate)+(7/24)
                        And also some negative duration ??
                        if you do ENDDTTM-BEGINDTTM you will get the duration in days(You dont need to use TO_CHAR to find the duration). If you want the duration in hours, use as
                        (ENDDTTM-BEGINDTTM)*24
                        How can I have the total of duration ?
                        Provide sample data (CREATE TABLE and INSERT statements), expected output and your DB version..
                        • 9. Re: between ( 9 PM) and (7 AM)
                          Etbin
                          Good news maybe. Chances are I wasn't that wrong. Lucky for sure :)
                          The OP should have used plain English in the first place as rp0428 suggested.
                          • 10. Re: between ( 9 PM) and (7 AM)
                            jeneesh
                            Etbin wrote:
                            Good news maybe. Chances are I wasn't that wrong. Lucky for sure :)
                            The OP should have used plain English in the first place as rp0428 suggested.
                            Did you actually mean the below ..? :(

                            and begindttm between trunc(*begindttm*) + 21 / 24 and trunc(*begindttm*) + 31 / 24
                            • 11. Re: between ( 9 PM) and (7 AM)
                              Etbin
                              Yes.
                              My interpretation was: find the durations (plus some additional data) of something that started between 9 pm and 7 am (of the next day) - period.
                              No relation to sysdate or whatever, but I might be well wrong this time too.
                              Frank cleverly resolved it within the same day, so my first doubt of having misunderstood the problem, but the OP seems not to be happy about that.
                              Just the usual situation: we having to guess - I found ther's much fun doing it - it's stimulating to meet other ideas.

                              Regards

                              Etbin

                              Edited by: Etbin on 20.2.2013 8:33
                              the OP might be looking for some maybe unauthorized or just off duty hours operation/action
                              • 12. Re: between ( 9 PM) and (7 AM)
                                jeneesh
                                Etbin wrote:
                                Yes.
                                Then it was logical to give just as "and begindttm >= trunc(begindttm) + 21 / 24 "

                                Right?Or I missed something..?
                                • 13. Re: between ( 9 PM) and (7 AM)
                                  user522961
                                  Thanks.
                                  DB version is 11g R2. It is a system table (peoplesoft) and insert is automaticaly when batch jobs run.
                                    CREATE TABLE "SYSADM"."PSPRCSRQST" 
                                     (     "PRCSINSTANCE" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
                                       1 MAXEXTENTS 2147483645
                                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                                    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                                    TABLESPACE "PSINDEX" ;
                                  • 14. Re: between ( 9 PM) and (7 AM)
                                    jeneesh
                                    {message:id=9360002}
                                    1 2 Previous Next