# Thread: Functions for converting intervals to seconds

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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;```

#### Posting Permissions

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