CODEWITHSUNDEEP
sqloracle-database
Roger
Roger

Reputation: 95

calculate average time in sec from two dates in oracle

i use below query,

SELECT CASE WHEN val_desc = 'united states' THEN 'USD',
            WHEN val_desc = 'ITALY'         THEN 'EUR' END AS key_p,
       avg((a.end_date - a.start_time)*24*60*60) as average, VAL_DESC
from empl a
group by VAL_DESC

expected output as (in seconds). 2.3 USD 1.2 EUR

hint:- we have records in tables those (a.end_date - a.start_time) will have 1 sec, or 2 sec or 3 and so no...so i need a average of it.

Upvotes: 0

Views: 233

Answers (2)

Popeye
Popeye

Reputation: 35900

Your question is little bit unclear for me.

So I am giving generic answer hoping that it will give you proper direction.

Oracle dates consists of date + time till seconds. And difference in dates returns number of days between dates.

Let's take a simple example

First record:

start_date = 16-dec-2019 12:25:01 
End_date = 16-dec-2019 12:26:01
Diff: 60 seconds

Second record:

start_date = 15-dec-2019 09:25:01 
End_date = 15-dec-2019 10:25:01 
Diff: 3600 seconds

Following queries will help you understand the logic

-- 1 -- average in number of days (around 1/24 will be the result)
Select avg(end_date-start_date) 
From your_table

-- 2 -- average in number of hours (around 0.5 will be the result)
Select avg(end_date-start_date) * 24
From your_table

-- 3 -- average in number of minutes (30.5 will be the result)
Select avg(end_date-start_date) * 24 * 60
From your_table

-- 4 -- average in number of seconds (1830 will be the result)
Select avg(end_date-start_date) * 24 * 60 * 60
From your_table

Cheers!!

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I suspect that you want two rows in the result set and the GROUP BY is throwing you off. If so:

SELECT (CASE WHEN val_desc = 'united states' THEN 'USD'
             WHEN val_desc = 'ITALY' THEN 'EUR'
        END) AS key_p, 
       AVG((a.end_date - a.start_time)*24*60*60) as average
FROM empl e 
GROUP BY (CASE WHEN val_desc = 'united states' THEN 'USD'
               WHEN val_desc = 'ITALY' THEN 'EUR'
          END);

Upvotes: 2

Related Questions