Re: Compute difference between timestamps

From: <c8s3dw_at_verizon.net>
Date: 4 Jun 2006 10:28:54 -0700
Message-ID: <1149442134.825631.82350@h76g2000cwa.googlegroups.com>

Mladen Gogala wrote:
> On Sat, 03 Jun 2006 11:06:51 -0700, c8s3dw wrote:
>
> > In Oracle database, given two timestamp columns, how do I compute the
> > difference, expressed in seconds, to three decimal places? i.e. 452.936
>
> By converting them to date with to_date function and then multiplying by
> the number of seconds in the normal day. Under certain circumstances, day
> can have 23 or 25 hours. You don't want one of those days.

Cannot be implemented directly because to_date takes a character column argument, not a timestamp. So we convert to character first:

select to_char(t1, 'DD-MON-YYYY HH24:MI:SS.FF3') C1 from t;

C1

---

03-JUN-2006 19:23:52.846 Now attempt to convert to date:

select to_date(to_char(t1, 'DD-MON-YYYY HH24:MI:SS.FF3'),

        'DD-MON-YYYY HH24:MI:SS.FF3') D1
from t;

ERROR at line 2:
ORA-01821: date format not recognized

TO_DATE does not take "FF3". Also, date lacks the millisecond precision that I need. Received on Sun Jun 04 2006 - 12:28:54 CDT