Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to calculate time difference between two columns?

fahmedarainDec 10 2010 — edited Dec 10 2010
Hi,


How to calculate time difference between two dates columns?


thx

Edited by: user12007410 on Dec 10, 2010 2:03 AM

Comments

Arun Kumar Gupta
SELECT (col1 - col2) AS difference from table_name

It will give you output in Days.

Regards
Arun
Nimish Garg
try this, u have have to modify it a little

http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html

http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post.html

http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post_23.html
fahmedarain
Dear,

i need output in minutes
Arun Kumar Gupta
Then use this

SELECT (col1 - col2)*24*60 AS difference_in_minutes from table_name

like this
SQL>SELECT (SYSDATE - TRUNC(SYSDATE))*24*60 difference_in_minutes 
  2    FROM DUAL;

DIFFERENCE_IN_MINUTES
---------------------
           251.533333

SQL>
Regards
Arun

Edited by: Arun Kumar Gupta on Dec 10, 2010 3:41 PM
fahmedarain
Hi arun,


Can you please explain query and its output for me?


thx
BluShadow
What needs explaining?

You take one date away from another wich gives you the difference in number of days (including fractions). Multiply that value by the number of minutes in a day (24 hours * 60 minutes) to find out what it is in minutes.

Basic mathematics.
789895
Hi,

Try this
SELECT TO_TIMESTAMP(COL1) - TO_TIMESTAMP(COL2) FROM TABLE_NAME;
cheers

VT
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 7 2011
Added on Dec 10 2010
#11g, #data, #database, #dba, #developer, #plsql, #sql
7 comments
115,269 views