CODEWITHSUNDEEP
sqlpostgresqlaveragedata-analysissql-function
Madhu
Madhu

Reputation: 429

How to calculate average of time with respect to midnight in SQL?

I have below data - I need to calculate the average of this times with respect to midnight time in SQL, Can you please tell me how should I calculate? If I convert them in seconds and simply take average I get ans as : 10:17:20 which is wrong . I need ans should be somewhere near to 23:00 

0:06:43
0:10:26
0:12:49
0:18:21
0:19:35
0:22:40
1:57:01
2:21:41
15:56:12
16:40:22
21:31:45
23:26:56
23:38:29
23:38:30
23:38:33

Consider this small example. - my data is 

2:21:41
0:19:35
0:18:21
23:38:33

for this I should get the ans as 00:30 ... Can you please let me know how I should calculate this in SQL?

Upvotes: 0

Views: 269

Answers (1)

Adam
Adam

Reputation: 5599

My idea is to change time to interval. This way you can shift your values to be around midnight by having negative values:

CASE WHEN value > '12:00:00' THEN value - time '24:00' ELSE value - time '00:00' END

See time - time operator in the documentation.

You'll get:

00:06:43    ->   00:06:43
00:10:26    ->   00:10:26
00:12:49    ->   00:12:49
00:18:21    ->   00:18:21
00:19:35    ->   00:19:35
00:22:40    ->   00:22:40
01:57:01    ->   01:57:01
02:21:41    ->   02:21:41
15:56:12    ->  -08:03:48
16:40:22    ->  -07:19:38
21:31:45    ->  -02:28:15
23:26:56    ->  -00:33:04
23:38:29    ->  -00:21:31
23:38:30    ->  -00:21:30
23:38:33    ->  -00:21:27

Now you can calculate average value. For the data specified you'll get -00:54:39.8 which is 23:05:20.2 (for negative value you need to + time '24:00').

So, the final formula is:

AVG(
    CASE WHEN value > '12:00:00' THEN value - time '24:00' ELSE value - time '00:00' END
) + time '24:00'

You can always do + time '24:00' because time will be wrapped if exceeds 24h.

Upvotes: 1

Related Questions