Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Functions for converting intervals to seconds

    There is a really old thread about this but it was suggested that I start a new thread with just this.

    Here are a couple functions that I borrowed/modified to convert an interval into seconds and to get the difference between two timestamps in seconds instead of an interval.

    This uses abs() so this will always return a positive value, regardless of
    which of the two timestamps is earlier or later. If you care about whehter the result represents the future or past, you'll need to remove the abs().

    PROMPT CREATE OR REPLACE FUNCTION timestamp_diff_in_seconds
    CREATE OR REPLACE function timestamp_diff_in_seconds (ts1 in timestamp, ts2 in timestamp)

    return number is total_secs number;

    diff interval day(9) to second(6);

    begin

    diff := ts2 - ts1;

    total_secs := abs(extract(second from diff) + extract(minute from diff)*60 + extract(hour from diff)*60*60 + extract(day from diff)*24*60*60);



    return total_secs;

    end timestamp_diff_in_seconds;
    /

  2. #2
    Join Date
    Mar 2010
    Posts
    5

    Interval to number (seconds+fractions) function

    PROMPT CREATE OR REPLACE FUNCTION dsinterval_to_number
    CREATE OR REPLACE function dsinterval_to_number ( x IN INTERVAL DAY TO SECOND)
    return number is total_secs number;
    diff interval day(9) to second(6) := x;
    begin

    total_secs := abs(extract(second from diff) + extract(minute from diff)*60 + extract(hour from diff)*60*60 + extract(day from diff)*24*60*60);

    return total_secs;

    end dsinterval_to_number;
    /

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Wrap your code in code tags. This is a snippet from the temporal toolkit that I wrote.
    Code:
    CREATE OR REPLACE FUNCTION dsinterval_to_seconds(ival INTERVAL DAY TO SECOND)
    RETURN NUMBER IS
    BEGIN
      RETURN EXTRACT(second FROM ival)  + 
        EXTRACT(minute FROM ival) * 60   +
        EXTRACT(hour FROM ival) * 3600   +
        EXTRACT(day FROM ival) * 86400;
    END;

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    And to convert back:
    Code:
    CREATE OR REPLACE FUNCTION seconds_to_dsinterval(sec NUMBER) 
    RETURN INTERVAL DAY TO SECOND IS
    BEGIN
      RETURN sec * INTERVAL '1' SECOND;
    END;

Tags for this Thread

Posting Permissions

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