HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Use data type arithmetic to convert from 'Timestamp' world to 'Date' world and opposite way
ORACLE STUFFS
09-Mar-2013
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   
        ,INTERVAL'0'MINUTE as ZERO_INTERVAL 
  from dual     

Select 
  TS_COL1+0 as TS2DATE 
 ,(DATE_COL1-TIMESTAMP'2000-01-01 00:00:00')+TIMESTAMP'2000-01-01 00:00:00'  as DATE2TS 
 ,DATE_COL2-DATE_COL1 as DATES2DAYS 
 ,(DATE_COL2-DATE_COL1)*INTERVAL'1'DAY as DATES2INTERVAL 
 ,(TS_COL2+0)-(TS_COL1+0) as TSS2DAYS 
 ,TS_COL2-TS_COL1 as TSS2INTERVAL 
 ,L1.* 
from L1

[Download]


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


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

TS_COL1+0 as TS2DATE 




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

DATE_COL2-DATE_COL1 as DATES2DAYS 




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

(DATE_COL2-DATE_COL1)*INTERVAL'1'DAY as DATES2INTERVAL 




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

TS_COL2-TS_COL1 as TSS2INTERVAL 




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