...

Datetime Summary

 
SQL(179)

【Question】
I have the following datetime records retrieved in SQL. Now I want to get some time-related values.
2014-9-1 10: 00  2014-9-2 11: 30
2014-9-1 10: 30  2014-9-2 11: 00
2014-9-3 11: 00  2014-9-4 12: 00
The datetime in the 2nd row is contained in that in the first row. How can I get the total duration?

 

【Answer】

You can manipulate it with Oracle CONNECT BY. But the clause is difficult to understand due to SQL’s lack of support of ordered sets. It becomes even harder if the computing task requires more, like summarize the overlapped time periods. In this case, if the data volume is small, you can handle it in SPL (Structured Process Language) to get simple and flexible code:

A

1

$select * from SHIP_BERTH   order by S

2

=A1.group@i(S>max(E[,-1]))

3

=A2.new(~.min(S):   S,~.max(E): E)

4

=A3.sum(interval@s(S,E))

5

=A1.select(E>E[-1]).sum(interval@s(max(S,E[-1]),E))

A1: Retrieve rows ordered by S in SQL.

A2: Group together rows where the datetimes overlap.

A3: Create a new table sequence; get from each group the min datetime as S and the max datetime as E.

A4: Besides the detail data, if you also want to know the total duration, use interval() function to get the duration in each group and then perform a sum.

A5: If you just need to know the total duration, you can remove the overlapped time period and then perform a sum.

See How to Call an SPL Script in Java to learn more about how to call an SPL script from a Java application.

 


SQL(179)
 
 •  372 View  •