8 Replies Latest reply on Jan 7, 2013 9:40 PM by Frank Kulash

    Delete rows between two particular timestamps

    947163
      Hi,

      Have a table which has 3 columns id,name,time where time is of datatype timestamp and it stores the time when the row was inserted. Need an query which accepts 2 parameters as input Ex: Start_Time,End_Time and all the rows in between the above mentioned timestamps must be deleted.
        • 1. Re: Delete rows between two particular timestamps
          sb92075
          944160 wrote:
          Hi,

          Have a table which has 3 columns id,name,time where time is of datatype timestamp and it stores the time when the row was inserted. Need an query which accepts 2 parameters as input Ex: Start_Time,End_Time and all the rows in between the above mentioned timestamps must be deleted.
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Delete rows between two particular timestamps
            Frank Kulash
            Hi,

            That sound like a job for the BETWEEN operator:
            DELETE     table_x
            WHERE     time_inserted BETWEEN start_time
                            AND     end_time
            ;
            BETWEEN works the same for all the built-in data types. That is, "x BETWEEN y AND z" works the same if x, y, and z are all NUMBERS, or if they are all DATEs, or if they are all TIMESTAMPs.

            Depending on your front end, there may not be any way to pass TIMESTAMP parameters to the query. If that's the case, make the parameters strings, and use TO_TIMESTAMP to convert those string to TIMESTAMPs in the query, like this:
            VARIABLE  start_time  VARCHAR2 (30)
            EXEC     :start_time := ...
            
            
            DELETE     table_x
            WHERE     time_inserted BETWEEN TO_TIMESTAMP (:start_time, ...)
                            AND     TO_TIMESTAMP (:end_time,      ...)
            ;
            As mentioned before, see the forum FAQ {message:id=9360002}
            Since passing parameters depends on your front end, you'll have to say what front end you're using (e.g., SQL*Plus version 10.1).
            • 3. Re: Delete rows between two particular timestamps
              947163
              I have difficulty in trying to execute the script. I need to pass two parameters one being start_timestamp and end_timestamp and in both of them i am passing timestamp values and when i execute the script i get the following error

              Error at Command Line:16 Column:67
              Error report:
              SQL Error: ORA-00904: "END_TIMESTAMP": invalid identifier
              00904. 00000 - "%s: invalid identifier"
              *Cause:   
              *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
              • 4. Re: Delete rows between two particular timestamps
                Warren Tolentino
                try to post the source code of the script that you are executing and also how you execute the script which shows how the parameters are being used.
                • 5. Re: Delete rows between two particular timestamps
                  947163
                  VARIABLE start_time timestamp(6)
                  VARIABLE end_time timestamp(6)
                  EXEC :start_time := &1
                  EXEC :end_time := &2


                  DELETE     abc1 WHERE     CRE_DT_TME BETWEEN start_time AND end_time
                  ;

                  I am trying to execute through SQLDeveloper. Please correct if the above script is wrong.
                  • 6. Re: Delete rows between two particular timestamps
                    Frank Kulash
                    Hi,
                    944160 wrote:
                    VARIABLE start_time timestamp(6)
                    VARIABLE end_time timestamp(6)
                    EXEC :start_time := &1
                    EXEC :end_time := &2


                    DELETE     abc1 WHERE     CRE_DT_TME BETWEEN start_time AND end_time
                    ;

                    I am trying to execute through SQLDeveloper. Please correct if the above script is wrong.
                    What happens when you run that? Do you get the results you want, or do you get an error message, such as
                    Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                                        VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                                        NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                                        BINARY_FLOAT | BINARY_DOUBLE ] ]
                    ?
                    You may need to declare the bind variables as VARCHAR2, and use TO_TIMESTAMP in your SQL statement to convert them to TIMESTAMPs

                    There's no point in using both substitution variables (such as &1) and bind variables (such as :start_time). Given that you're using substitution variables, you can use them directly in your SQL statement:
                    DELETE     abc1 
                    WHERE     cre_dt_tme BETWEEN TO_TIMESTAMP ('&1', 'DD-Mon-YYYY')     -- I'm jst guessing at the format
                                 AND         TO_TIMESTAMP ('&2', 'DD-Mon-YYYY')
                    ;
                    Edited by: Frank Kulash on Jan 7, 2013 4:20 PM
                    • 7. Re: Delete rows between two particular timestamps
                      947163
                      So if i do that will i be able to delete rows between 2 timestamps accurately..? Or else will the time be rounded off..? Since i am passing a varchar input.
                      • 8. Re: Delete rows between two particular timestamps
                        Frank Kulash
                        Hi,
                        944160 wrote:
                        So if i do that will i be able to delete rows between 2 timestamps accurately..?
                        It will be as accurate as the values you pass in. If you're careful about the values you pass in, then you can get the results you want.
                        Or else will the time be rounded off..? Since i am passing a varchar input.
                        If you don't specify parts of the TIMESTAMP, they will be given default values (truncated, not rounded).
                        In particular, if you don't specify the hours, minutes and seconds in TO_TIMESTAMP, they will default to 00:00:00.000000.

                        Remember that BETWEEN always includes both end-points. When working with TIMESTAMPs and DATEs, it's often inconvenient to use the upper bound. A lot of folks use 2 separate conditions instead, e.g.
                        DELETE     abc1 
                        WHERE     cre_dt_tme >= TO_TIMESTAMP ('&1', 'DD-Mon-YYYY')     -- I'm jst guessing at the format
                        AND     cre_dt_tme <  TO_TIMESTAMP ('&2', 'DD-Mon-YYYY') + INTERVAL '1' DAY
                        ;
                         

                        I hope this answers your question.
                        If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
                        If you're asking about a DML statement, such as DELETE, 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.
                        If your problem involves parameters (such as :start_time) then post a few different sets of parameters, and the results you want from the same sample data for each set.
                        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}
                        Didn't I mention all this earlier? The longer you delay posting the infiormation we need, the longer you'll hve to wait for a solution.