# Thread: Converting an Interval Day to Second to an Integer

1. Registered User
Join Date
Oct 2004
Posts
5

## Unanswered: Converting an Interval Day to Second to an Integer

Does anyone know if you can convert an interval day to second to an integer? If so, how do you do it?

thanks,
Laura

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>If so, how do you do it?
you need to use "higher math" ; specifically multiplication.
How many hours are in 1 day?
How many minutes are in 1 hour?
How many seconds are in 1 minute?

3. Registered User
Join Date
Oct 2004
Posts
5

## what's the syntax

I understand that I need to do some math here; but everything I try doesn't work. Do you know the syntax for this?

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
X_days*24*60*60 = N_seconds

:-(

5. Registered User
Join Date
Oct 2004
Posts
5

## it doesn't work

Thanks anyway but that didn't work.

My table has a field called "race_time" and the data type is INTERVAL DAY TO SECOND. So a given race_time is 3:20 or 2:40.

SELECT (race_time*24*60*60) from TABLEWHATEVER;

This still yields an interval *not* an integer.

Any other ideas?

L

6. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>My table has a field called "race_time"
IRRELEVANT and meaningless to this discussion.

>the data type is INTERVAL DAY TO SECOND.
I don't believe this is a true statement
From the SQL Reference manual
The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

>SELECT (race_time*24*60*60) from TABLEWHATEVER;
>This still yields an interval *not* an integer.
There are only two type of number integers (no fractional part); [a.k.a. whole number] and numbers which are not whole.

SQL> DESC TABLEWHATEVER -- which contains the field "RACE_TIME".

7. Registered User
Join Date
Oct 2004
Posts
5

## sorry you're wrong

Please see http://www.oracle.com/technology/pro...ily/Aug28.html where it clearly states that INTERVAL DAY TO SECOND is in fact a date data type. If you are not familiar with this, then you can't help me. A condescending tone is inappropriate. I do not need any further comments from you. Thanks.

L

8. Registered User
Join Date
Apr 2004
Location
Posts
249
Try using the "Extract" function and a little bit of anacedent's "higher mathematics".

Here is some example code that you may be able to use as a guide.

PHP Code:
```    set serveroutput on size 1000000      DECLARE         day_to_sec INTERVAL DAY TO SECOND := TO_DSINTERVAL('0 1:30:45.5');            num_val  number(20);      BEGIN         dbms_output.put_line('Interval in seconds = ') ;      num_val := to_number(extract(second from day_to_sec)) +              to_number(extract(minute from day_to_sec)) * 60 +               to_number(extract(hour from day_to_sec))   * 60 * 60 +               to_number(extract(day from day_to_sec))  * 60 * 60* 24;      dbms_output.put_line(num_val);      END;   /  ```
And by the way, thanks for telling us about the interval datatype. I was not aware of it and had to look up OTN (Oracle Technology Network) documentation)and find out what it was.

Ravi

9. Registered User
Join Date
Oct 2004
Posts
5

## thanks

Groovy! I'll give it a try!

Laura

10. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
However, since you are adding the return of the interval in seconds to a fixed precision number variable, the fractional part of the second is lost.

11. Registered User
Join Date
Jun 2003
Location
Ottawa
Posts
105

## haha

Originally Posted by llchrist
Please see http://www.oracle.com/technology/pro...ily/Aug28.html where it clearly states that INTERVAL DAY TO SECOND is in fact a date data type. If you are not familiar with this, then you can't help me. A condescending tone is inappropriate. I do not need any further comments from you. Thanks.
L

burn! good job.

12. Registered User
Join Date
Sep 2004
Location
London, UK
Posts
565
The two INTERVAL datatypes are also documented on the page anacedent referred to, right after the TIMESTAMPs.

13. Registered User
Join Date
Sep 2004
Location
London, UK
Posts
565
Code:
```SQL*Plus: Release 9.2.0.1.0 - Developer's Release

SQL> CREATE TABLE testit
2  ( race_time INTERVAL DAY (3) TO SECOND (3) );

Table created.

SQL> desc testit
Name                                 Null?    Type
------------------------------------ -------- ----------------------------
RACE_TIME                                     INTERVAL DAY(3) TO SECOND(3)

SQL> INSERT INTO testit VALUES (INTERVAL '22' HOUR / 7);

1 row created.

SQL> col race_time format a20

SQL> SELECT race_time
2        , (TRUNC(SYSDATE) + race_time - TRUNC(SYSDATE)) * 86400 AS seconds
3   FROM testit;

RACE_TIME               SECONDS
-------------------- ----------
+000 03:08:34.286         11314

1 row selected.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';

Session altered.

SQL> SELECT TRUNC(SYSDATE) + INTERVAL '11314' SECOND AS test FROM dual;

TEST
--------
03:08:34

1 row selected.```

14. Registered User
Join Date
Apr 2009
Posts
6

## The solution

Originally Posted by WilliamR
Code:
```SQL*Plus: Release 9.2.0.1.0 - Developer's Release
2        , (TRUNC(SYSDATE) + race_time - TRUNC(SYSDATE)) * 86400 AS```
Two issue with this approach. a) loses the fractions. b)can off by 86400 seconds under certain condition.

Update: b) is not an issue for SQL statement. Thanks shammat for the clarification.

See Program It: Converting Oracle Interval Data Type to Seconds
Last edited by kennethxu; 04-29-09 at 00:44.

15. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776