Use data type arithmetic to convert from 'Timestamp' world to 'Date' world and opposite way

DATA ARCHITECTURE

by Ludek Bob Jankovsky, 09-Mar-2013 (ORACLE STUFFS)

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