Extracting the total number of seconds from an interval data-type

Question

When subtracting timestamps the return value is an interval data-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer.

The following would work, but it's not very pretty:

select abs( extract( second from interval_difference ) 
          + extract( minute from interval_difference ) * 60 
          + extract( hour from interval_difference ) * 60 * 60 
          + extract( day from interval_difference ) * 60 * 60 * 24
            )
  from ( select systimestamp - (systimestamp - 1) as interval_difference
           from dual )

Is there a more elegant method in SQL or PL/SQL?

:somewhere i have found this ` select (TRUNC(SYSDATE) + out.interv - TRUNC(SYSDATE)) * 86400 from (select systimestamp -(systimestamp -1) as interv from dual )out` — Gaurav Soni, Apr 10 '12 at 20:37
since adding the return of the interval in seconds to a fixed precision number variable, the fractional part of the second is lost in the query mentioned in comments — Gaurav Soni, Apr 10 '12 at 20:40
Although it may not seem elegant, I still prefer this basic solution as it does not suffer from "ORA-01873: the leading precision of the interval is too small" that easily. — Blaf, Mar 26 '18 at 11:28

Zhaoping Lu · Answer 1 · 2016-08-12T03:47:57.633

31

An easy way:

select extract(day from (ts1-ts2)*86400) from dual;

The idea is to convert the interval value into days by times 86400 (= 24*60*60). Then extract the 'day' value which is actually second value we wanted.

edited Aug 12 '16 at 03:47

answered Aug 12 '16 at 03:32

Zhaoping Lu

426
4
6

6

I adapted this to use `extract(day from (ts1-ts2)*86400*1000) / 1000` to get millisecond precision. – Thomas W Dec 06 '16 at 04:55
5

This works only for small intervals - under 1000s. After that, you'll hit the wall with `ORA-01873: the leading precision of the interval is too small` – Štefan Oravec Sep 16 '20 at 09:26
@ŠtefanOravec I did a quick test. The biggest interval I can get is 11574 days in Oracle 11.2.0.4 as below: > select extract(day from (systimestamp - (systimestamp - 11574))*86400) seconds from dual; SECONDS ---------- 999993600 – Zhaoping Lu Sep 17 '20 at 12:57
@ZhaopingLua there is some internal data type conversion magic apparently in your test case, as it fails with interval datatype - `select extract(day from (numtodsinterval(10, 'hour')) * 86400) from dual;` vs `select extract(day from (numtodsinterval(1000, 'second')) * 86400) from dual;` (Oracle 18cXE) – Štefan Oravec Jun 09 '21 at 16:04
@ZhaopingLu better test with table and conversion from persisted value `create table test as select numtodsinterval(10, 'hour') as value from dual; select extract(day from (value) * 86400) from test;` – Štefan Oravec Jun 09 '21 at 16:09

zep · Accepted Answer · 2012-04-11T08:19:17.987

I hope this help:

zep@dev> select interval_difference
      2        ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
      3  from   (select systimestamp - (systimestamp - 1) as interval_difference
      4          from   dual)
      5 ;

INTERVAL_DIFFERENCE                                                             FRACT_SEC_DIFFERENCE
------------------------------------------------------------------------------- --------------------
+000000001 00:00:00.375000                                                                 86400,375

With your test:

zep@dev> select interval_difference
      2        ,abs(extract(second from interval_difference) +
      3        extract(minute from interval_difference) * 60 +
      4        extract(hour from interval_difference) * 60 * 60 +
      5        extract(day from interval_difference) * 60 * 60 * 24) as your_sec_difference
      6        ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
      7        ,round(sysdate + (interval_difference * 86400) - sysdate) as sec_difference
      8        ,round((sysdate + (interval_difference * 86400) - sysdate) * 1000) as millisec_difference
      9  from   (select systimestamp - (systimestamp - 1) as interval_difference
     10          from   dual)
     11  /

INTERVAL_DIFFERENCE                                                             YOUR_SEC_DIFFERENCE FRACT_SEC_DIFFERENCE SEC_DIFFERENCE MILLISEC_DIFFERENCE
------------------------------------------------------------------------------- ------------------- -------------------- -------------- -------------------
+000000001 00:00:00.515000                                                                86400,515            86400,515          86401            86400515

zep@dev>

I ended up explaining exactly why this works here: http://stackoverflow.com/a/17413839/458741 — Ben, Jul 03 '13 at 20:16
This doesn't work for some values of interval_difference. For example: select interval_difference, sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference from ( select systimestamp - (systimestamp - 1/3) as interval_difference from dual ); — Pedro Pedruzzi, Jul 27 '15 at 20:56

score 11 · Answer 3 · edited Jan 02 '15 at 18:41

11

I've found this to work. Apparently, if you do arithmetics with timestamps they are converted to some internal datatype that, when substracted from each other, returns the interval as a number.

Easy? Yes. Elegant? No. Gets the work done? Oh yeah.

SELECT ( (A + 0) - (B + 0) ) * 24 * 60 * 60 
FROM
(
   SELECT SYSTIMESTAMP A,
          SYSTIMESTAMP - INTERVAL '1' MINUTE B
   FROM DUAL
);

edited Jan 02 '15 at 18:41

Jon Heller

34,999
6
74
132

answered Jan 02 '15 at 08:06

Waldo

423
4
5

This only gives me whole seconds. – vikingsteve Feb 04 '15 at 11:35
1

Oops, true. The question spefically asked for micro/milliseconds. This is an easy way to give whole seconds, though. – Waldo Feb 11 '15 at 11:19
1

CAST(A AS DATE) is equivalent to (A+0) – Andrew Apr 15 '16 at 09:58
arithmetics with timestamps is not the same as converting interval type value stored in table – Štefan Oravec Jun 09 '21 at 16:11

score 4 · Answer 4 · edited May 02 '18 at 05:50

4

Unfortunately, I don't think that there is an alternative (or more elegant) way of calculating total seconds from an interval type in pl/sql. As this article mentions:

... unlike .NET, Oracle provides no simple equivalent to TimeSpan.TotalSeconds.

therefore extracting day, hour etc from the interval and multiplying them with corresponding values seems like the only way.

edited May 02 '18 at 05:50

Rob

26,989
16
82
98

answered Apr 10 '12 at 15:58

Korhan Ozturk

11,148
6
36
49

score 4 · Answer 5 · edited May 23 '17 at 12:34

4

Based on zep's answer, I wrapped things up into a function for your convenience:

CREATE OR REPLACE FUNCTION intervalToSeconds( 
     pMinuend TIMESTAMP , pSubtrahend TIMESTAMP ) RETURN NUMBER IS

vDifference INTERVAL DAY TO SECOND ; 

vSeconds NUMBER ;

BEGIN 

vDifference := pMinuend - pSubtrahend ;

SELECT EXTRACT( DAY    FROM vDifference ) * 86400
     + EXTRACT( HOUR   FROM vDifference ) *  3600
     + EXTRACT( MINUTE FROM vDifference ) *    60
     + EXTRACT( SECOND FROM vDifference )
  INTO
    vSeconds 
  FROM DUAL ;

  RETURN vSeconds ;

END intervalToSeconds ;

edited May 23 '17 at 12:34

Community

1
1

answered Jul 03 '13 at 17:00

Tomislav Matas

41
2

3

Thank you, but a function is far less efficient than SQL. You'll lose around a millisecond per record in the SQL/PL/SQL context switch alone. There's also no need to select from dual or to assign the result to a variable, you can return it directly. I'd write the same function like this: http://www.sqlfiddle.com/#!4/42d23/1. However, I'd like to emphasise that a function should not be used if at all possible. – Ben Jul 03 '13 at 17:12
Also, now I come to think about it the code is mine... I was asking if there's a better way of doing it! Welcome to Stack Overflow :-). – Ben Jul 03 '13 at 17:16

score 3 · Answer 6 · edited Jun 21 '18 at 11:37

3

Use following query:

select (cast(timestamp1 as date)-cast(timestamp2 as date))*24*60*60)

edited Jun 21 '18 at 11:37

Amit Verma

8,660
8
35
40

answered Sep 05 '17 at 09:15

moshik

1,424
1
10
6

1

this cast will truncate millisecond part – thangdc94 Oct 10 '22 at 07:38
moreover it will give wrong result if timestamps are in different time zones (e.g. if daylight saving changed in between) – mik Feb 08 '23 at 17:09

score 1 · Answer 7 · answered Sep 23 '19 at 12:06

1

Similar to @Zhaoping Lu answer but directly extracting seconds instead of getting them from the number of days.

SELECT extract(second from (end_date - start_date)) as "Seconds number"
FROM my_table

(worked on PostgresSQL 9.6.1)

answered Sep 23 '19 at 12:06

Louis Saglio

1,120
10
20

1

in both PostgresSQL and Oracle this will extract seconds only from the interval without including minutes, hours or days in the returning value, so e.g. 1 hour interval would return 0 – mik Feb 08 '23 at 17:21

Danton Estevam Pinto · Answer 8 · 2020-08-26T13:31:01.707

A shorter method to convert timestamp to nanoseconds.

SELECT (EXTRACT(DAY FROM (
    SYSTIMESTAMP --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'
- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM
    SYSTIMESTAMP --Replace line with desired timestamp
)) *  1000000000 AS NANOS FROM DUAL;

NANOS
1598434427263027000

A method to convert nanoseconds to timestamp.

SELECT TIMESTAMP '1970-01-01 00:00:00 UTC' + numtodsinterval(
    1598434427263027000 --Replace line with desired nanoseconds
/ 1000000000, 'SECOND') AS TIMESTAMP FROM dual;

TIMESTAMP
26/08/20 09:33:47,263027000 UTC

As expected, above methods' results are not affected by time zones.

A shorter method to convert interval to nanoseconds.

SELECT (EXTRACT(DAY FROM (
    INTERVAL '+18500 09:33:47.263027' DAY(5) TO SECOND --Replace line with desired interval --Maximum value: INTERVAL '+694444 10:39:59.999999999' DAY(6) TO SECOND(9) or up to 3871 year
) * 24 * 60) * 60 + EXTRACT(SECOND FROM (
    INTERVAL '+18500 09:33:47.263027' DAY(5) TO SECOND --Replace line with desired interval
))) * 1000000000 AS NANOS FROM DUAL;

NANOS
1598434427263027000

A method to convert nanoseconds to interval.

SELECT numtodsinterval(
    1598434427263027000 --Replace line with desired nanoseconds
/ 1000000000, 'SECOND') AS INTERVAL FROM dual;

INTERVAL
+18500 09:33:47.263027

Replace 1000000000 by 1000, for example, if you'd like to work with milliseconds instead of nanoseconds.

I've tried some of posted methods, but a got the exception "ORA-01873: the leading precision of the interval is too smalll" when multiplying the interval by 86400, so I've decided do post the methods that works for me.

Iain Ballard · Answer 9 · 2023-06-06T14:41:35.493

For Postgresql, try

SELECT EXTRACT(EPOCT from (end_date - start_date))
FROM my_table

The EPOCH gives the total seconds of the whole duration, as a floating-point value.

From https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

epoch For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (negative for timestamps before that); for date and timestamp values, the nominal number of seconds since 1970-01-01 00:00:00, without regard to timezone or daylight-savings rules; for interval values, the total number of seconds in the interval

score -2 · Answer 10 · answered Jun 21 '18 at 07:59

-2

SELECT to_char(ENDTIME,'yyyymmddhh24missff')-to_char(STARTTIME,'yyyymmddhh24missff') AS DUR FROM DUAL; yyyymmddhh24miss- WILL GIVE DURATION IN SEC yyyymmddhh24mi DURATION IN MIN yyyymmddhh24 - DURATION - HOURS yyyymmdd DURATION IN DAYS

answered Jun 21 '18 at 07:59

Arun Kumar

11
1

3

Please format the code and explain the answer you posted. – Björn Zurmaar Jun 21 '18 at 08:23

Extracting the total number of seconds from an interval data-type

10 Answers10

Linked