Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    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. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    X_days*24*60*60 = N_seconds

    :-(
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    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. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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
    http://download-west.oracle.com/docs...ts2a.htm#45443
    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.

    Please post the results of
    SQL> DESC TABLEWHATEVER -- which contains the field "RACE_TIME".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    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. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    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 6024;
          
    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. #9
    Join Date
    Oct 2004
    Posts
    5

    thanks

    Groovy! I'll give it a try!

    Laura

  10. #10
    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. #11
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    haha

    Quote 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. #12
    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. #13
    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. #14
    Join Date
    Apr 2009
    Posts
    6

    The solution

    Quote 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. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    One issue with this post is that this thread is more than 4 years old & should Rest In Peace.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •