HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Use data type arithmetic to convert from 'Timestamp' world to 'Date' world and opposite way
There is not straight conversion between the TIMESTAMP and DATE datatypes. Easy way to 'misuse' the standard data type arithmetic to do particular conversions in our queries.
Following article will briefly explain the ways.

For easier check you can prepare following example code, allowing to learn about the behavior by an example:

With l1 as( 
  Select DATE'2013-05-25' as DATE_COL1 
        ,DATE'2013-05-29' as DATE_COL2     
        ,TIMESTAMP'2013-05-25 00:00:00.000' as TS_COL1 
        ,TIMESTAMP'2013-05-29 00:00:00.000' as TS_COL2   
  from dual     

  TS_COL1+0 as TS2DATE 
 ,(DATE_COL1-TIMESTAMP'2000-01-01 00:00:00')+TIMESTAMP'2000-01-01 00:00:00'  as DATE2TS 
 ,(TS_COL2+0)-(TS_COL1+0) as TSS2DAYS 
from L1


In following part we will explain particular cases of the conversion issues:

Timestamp to Date
uses fact, that TS + NUMBER -> DATE


Date to Timestamp
uses fact, that DATE - TS -> INTERVAL
We use subtraction and addition of the same TS constant.

(DATE_COL1-TIMESTAMP'2000-01-01 00:00:00')+TIMESTAMP'2000-01-01 00:00:00'  as DATE2TS 

Interval between dates in days
is very usual, uses fact, that DATE - DATE -> NUMBER


Interval between dates as interval
uses multiplication of number of days and the 1 day interval


Interval between timestamps in days
uses particular conversion of timestamps into dates, and then DATE - DATE -> NUMBER

(TS_COL2+0)-(TS_COL1+0) as TSS2DAYS 

Interval between timestamps as an interval
is very usual, uses standard conversion arithmetic


Average Interval
Intervals are very useful to have time displayed, it solves the problem of time units and displays the interval in readable format. Anyway there is one hardship. Once you try to aggregate intervals to seek average duration, it returns following error:

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

There is following possible workaround:
Convert interval into number of days, get the average and convert the aggregation back to interval

AVG((end_time+0)-(start_time+0))*INTERVAL'1'DAY as DUR

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky