7 Replies Latest reply: Nov 24, 2009 2:35 AM by 542647 RSS

    To_timestamp query

    542647
      I have 2 date columns in one table. I want to find the difference between 2 dates in minutes. So I have used to_timestamp function.
      But when I convert using to_timestamp function on any column, it shows value for time as 03/10/2009 12:00:00 but in actual I was getting
      03/10/2009 05:30:50 even if it was a date column. What should I do to extract value in minutes.
      To find the difference in minutes from 2 columns having datatype as Date.
        • 1. Re: To_timestamp query
          SatishKandi
          SQL> select sysdate - to_date ('01.01.2009', 'dd.mm.yyyy') in_days, (sysdate - to_date ('01.01.2009', 'dd.mm.yyyy') ) * 1440 diff_in_minutes from dual;
          
                   IN_DAYS  DIFF_IN_MINUTES
          ---------------- ----------------
                       328          471,652
          • 2. Re: To_timestamp query
            542647
            Thanx satish, but I am looking for something like
            Select a_date,b_date from abc table;
            
            a_date                                  b_date
            10/10/2009 10:50:10 PM     10/11/2009 01:40:10 AM
            
            Select b_date - a_date  from abc;
            
            00:00:00 02:50:00
            as the difference between 2 dates is only 2 hours 50 minutes.
            a_date and b_date are of Date datatype

            Edited by: user539644 on Nov 24, 2009 1:07 PM
            • 3. Re: To_timestamp query
              SatishKandi
              Do you mean you want to ignore the DATE part of the date column and just compute the difference in time in minutes?
              SQL> select (to_date ('10/11/2009 01:40:10 AM', 'mm/dd/yyyy hh:mi:ss am') - to_date ('10/10/2009 10:50:10 PM', 'mm/dd/yyyy hh:mi:ss am')) * 1440 diff from dual;
              
                          DIFF
              ----------------
                           170
              Edited by: Satish Kandi on Nov 24, 2009 1:10 PM

              Example added.
              • 4. Re: To_timestamp query
                542647
                Yes I want to extract the difference between 2 dates in hours, minutes and seconds.
                • 5. Re: To_timestamp query
                  542647
                  I want output like what I have mentioned above..
                  like for 2 hrs 10 minutes and 40 seconds it shows output like
                  02:10:40
                  • 6. Re: To_timestamp query
                    SatishKandi
                    Not sure if there is any better way of doing this.
                    SQL> with data as
                      2  (select (to_date ('10/11/2009 01:40:20 AM', 'mm/dd/yyyy hh:mi:ss am') -
                      3      to_date ('10/10/2009 10:50:10 PM', 'mm/dd/yyyy hh:mi:ss am'))diff
                      4  from dual)
                      5  select 'difference is '||
                      6  to_char (round (floor (diff * 1440 /60))) ||' hours '||
                      7  to_char (round (mod (diff * 1440, 60)))||' minutes '||
                      8  to_char (mod (diff * 86400, 60)) ||' seconds'
                      9  from data;
                    
                    'DIFFERENCEIS'||TO_CHAR(ROUND(FLOOR(DIFF*14
                    -------------------------------------------
                    difference is 2 hours 50 minutes 10 seconds
                    • 7. Re: To_timestamp query
                      542647
                      thanx satish