Re: Oracle 11.2.0.3 timediff function
Date: Sun, 14 Feb 2016 22:10:08 -0500
Message-ID: <56C14190.1090504_at_gmail.com>
On 02/14/2016 09:58 PM, Mladen Gogala wrote:
> On 02/14/2016 01:25 PM, MJ Chicago wrote:
>> Hello Oracle-L
>> Is anyone familiar if Oracle has a timediff function to calculate
>> difference in time (seconds/milliseconds/etc) in 11.2.0.3?
>>
> I am not sure about 11.2.0.3, I discarded that version last summer,
> but 12c has no problems with timestamp arithmetic:
>
> mgogala_at_umajor:~$ sqlplus scott/tiger_at_local
>
> SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 14 21:50:49 2016
>
> Copyright (c) 1982, 2014, Oracle. All rights reserved.
>
> Last Successful login time: Sun Feb 14 2016 21:49:48 -05:00
>
> Connected to:
> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
> Production
> With the Partitioning, OLAP, Advanced Analytics and Real Application
> Testing options
>
> SQL> select systimestamp - trunc(systimestamp) from dual;
>
> SYSTIMESTAMP-TRUNC(SYSTIMESTAMP)
> ---------------------------------------------------------------------------
>
> +000000000 21:51:16.526463
>
> Elapsed: 00:00:00.00
> SQL> ed
> Wrote file afiedt.buf
>
> 1* select extract(second from systimestamp - trunc(systimestamp))
> from dual
> SQL> /
>
> EXTRACT(SECONDFROMSYSTIMESTAMP-TRUNC(SYSTIMESTAMP))
> ---------------------------------------------------
> 20.757614
>
> Elapsed: 00:00:00.00
> SQL> select extract(hour from systimestamp - trunc(systimestamp)) from
> dual;
>
> EXTRACT(HOURFROMSYSTIMESTAMP-TRUNC(SYSTIMESTAMP))
> -------------------------------------------------
> 21
>
> Elapsed: 00:00:00.00
>
> All it takes to do time stamp difference is "-".
> Regards
>
I decided that I was not too lazy to power up my old desktop, which
still has 11G, and it's the same thing,
no problems whatsoever with timestamp subtraction.
mgogala_at_umajor:~$ sqlplus scott/tiger_at_ora11
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 14 22:06:37 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> select systimestamp - trunc(systimestamp) from dual;
SYSTIMESTAMP-TRUNC(SYSTIMESTAMP)
+000000000 22:06:38.863372
Elapsed: 00:00:00.00
SQL> select extract(second from systimestamp - trunc(systimestamp)) from
dual;
EXTRACT(SECONDFROMSYSTIMESTAMP-TRUNC(SYSTIMESTAMP))
15.250861
Elapsed: 00:00:00.00
SQL>
-- Mladen Gogala Oracle DBA Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 15 2016 - 04:10:08 CET