Difference between two timestamps (in days) in oracle including difference between timestamp

Question

I have a Table which has two fields with TimeStamp in Oracle DB. I have to calculate the difference between these two columns in Days including timestamp values.

Ex :- I have both column values as following than 
Column1 value = 07-DEC-16 11.05.24.500000000 PM 
Column2 value = 08-DEC-16 03.12.06.874000000 AM

Days Calculation should be shown as 1. Currently after doing search and reading many question on stackoverflow I came to know and now I am using following Query to calculate difference between these two columns

Select Extract( Day From Diff ) Days
From (Select B1.Column2 - B1.Column1 As Diff
From Table1 B1);

This query results in calculated Days as 0 instead of 1.

I am not getting what to be done, please Guide through. Do I need to convert it to different format? or something else ?

Answer 1

If you just want the difference from 2016-12-07 to 2016-12-08 (ignoring the time components) then:

SELECT TRUNC( column2 ) - TRUNC( column1 ) AS days
FROM   table1

If you want the difference rounded up to the nearest day then (ignoring the fractional seconds) you could do:

SELECT CEIL( CAST( column2 AS DATE ) - CAST( column1 AS date ) ) AS days
FROM   table1

If you want to include fractional seconds and round up to the nearest day then:

SELECT CEIL(
         TRUNC( column2, 'MI' )
         - TRUNC( column1, 'MI' )
         + EXTRACT( SECOND FROM column2 ) / (24*60*60)
         - EXTRACT( SECOND FROM column1 ) / (24*60*60)
       ) AS days
FROM   table1