Skip to Main Content
  • Questions

  • find the time spent on weekend and weekday between two dates with timestamp in Oracle 11g

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Stuti.

Asked: April 16, 2016 - 11:32 pm UTC

Last updated: May 12, 2016 - 2:32 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hello All

I have question where i want to find the time spent on weekdays and weekend between two dates

example

ID Starttime Endtime Weekday duration weekend duration
(mm-dd-yyyy) (mm-dd-yyyy) (min) (min)

1 04-01-2016 11:50:10 PM 04-02-2016 09:20:00 AM 9.83 560


I want to split the time spent over weekday and weekend.

Please help to provide a solution in oracle 11g

Regards
Stuti

and Connor said...

Is this something like what you had in mind ?


SQL> drop table t purge;

Table dropped.

SQL> create table t ( d_from date, d_to date );

Table created.

SQL>
SQL> insert into t values ( sysdate-35.123, sysdate+37.3546);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from t;

D_FROM    D_TO
--------- ---------
13-MAR-16 25-MAY-16

SQL> select to_char(d_from,'DY'), to_char(d_to,'DY') from t;

TO_ TO_
--- ---
SUN WED

SQL>
SQL>
SQL> with all_days as
  2  ( select d_from, d_to, trunc(d_from) + rownum - 1  dy
  3    from dual, t
  4    connect by level <= trunc(d_to) - trunc(d_from) + 1
  5  ),
  6  time_per_day as
  7    ( select
  8         case
  9           when dy < d_from then  d_from - dy   -- partial first day
 10           when trunc(d_to)=dy then d_to - dy   -- partial last day
 11           else 1
 12         end  time_elapsed,
 13         case when to_char(dy,'DY') in ('SAT','SUN') then 'Weekend' else 'Weekday' end tag
 14      from   all_days
 15    )
 16  select tag,
 17         sum(time_elapsed) days
 18  from time_per_day
 19  group by tag;

TAG           DAYS
------- ----------
Weekend 20.6158218
Weekday 52.0934144

SQL>
SQL>
SQL>


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very useful

Stuti, April 18, 2016 - 2:59 pm UTC

The reply to my question was in time and is very useful,what i was looking for.

Will surely come again for more queries

Need to get the code working on table with a ID column

Stuti, May 10, 2016 - 4:40 pm UTC

Hello Connor

The code you provided works fine when i have single row in the table(t). Can you help to provide some guidance when I refer a table with multiple rows to get this working?

Suppose I have table like

ID Starttime Endtime
123 2016-04-03 2016-04-16
234 2016-03-22 2016-04-05

Thanks in advance
Chris Saxon
May 11, 2016 - 1:01 am UTC

Can you clarify - do you want it broken down by *each* ID ?

Followup

Stuti, May 11, 2016 - 1:28 am UTC

Yes I want to have break down per id.

My target is like

ID wkdy_dur wknd_dur

Thanks
Stuti
Connor McDonald
May 11, 2016 - 2:04 am UTC 


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( id int, d_from date, d_to date );

Table created.

SQL>
SQL> insert into t
  2  select rownum,
  3         sysdate-dbms_random.value(1,10),
  4         sysdate+dbms_random.value(1,10)
  5  from dual
  6  connect by level <= 10;

10 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from t;

  ID D_FROM              D_TO
---- ------------------- -------------------
   1 07/05/2016 08:28:14 14/05/2016 17:45:52
   2 02/05/2016 17:57:51 17/05/2016 15:57:40
   3 06/05/2016 19:28:18 18/05/2016 00:47:37
   4 02/05/2016 00:51:46 20/05/2016 04:30:59
   5 07/05/2016 19:39:47 21/05/2016 09:01:55
   6 06/05/2016 16:11:18 16/05/2016 21:16:21
   7 07/05/2016 17:40:10 21/05/2016 08:15:55
   8 05/05/2016 13:55:21 18/05/2016 20:55:35
   9 09/05/2016 04:46:35 14/05/2016 22:33:12
  10 04/05/2016 06:45:30 18/05/2016 22:55:01

10 rows selected.

SQL>
SQL> with all_days as
  2  ( select id, d_from, d_to, trunc(d_from) + column_value - 1  dy
  3    from t,
  4         table(cast(multiset(
  5           select rownum r
  6           from   dual
  7           connect by level <= trunc(t.d_to) - trunc(t.d_from) + 1
  8           ) as sys.odcinumberlist ))
  9  ),
 10  time_per_day as
 11    ( select
 12         id,
 13         case
 14           when dy < d_from then  d_from - dy   -- partial first day
 15           when trunc(d_to)=dy then d_to - dy   -- partial last day
 16           else 1
 17         end  time_elapsed,
 18         case when to_char(dy,'DY') in ('SAT','SUN') then 'Weekend' else 'Weekday' end tag
 19      from   all_days
 20    )
 21  select id,
 22         tag,
 23         sum(time_elapsed) days
 24  from time_per_day
 25  group by id,tag;

  ID TAG           DAYS
---- ------- ----------
   3 Weekday 7.84438657
   5 Weekday         10
   8 Weekday 9.45203704
   9 Weekday  4.1990162
   1 Weekday          5
  10 Weekday 10.2364699
   6 Weekend          4
   7 Weekday         10
   4 Weekday 13.2241319
   5 Weekend   4.195625
   2 Weekday 11.4135532
   2 Weekend          4
   4 Weekend          4
   6 Weekday 6.56086806
   8 Weekend          4
   1 Weekend   2.093125
   7 Weekend 4.08061343
  10 Weekend          4
   3 Weekend          4
   9 Weekend .939722222

20 rows selected.

SQL>
SQL>


Awesome

Stuti, May 11, 2016 - 2:28 pm UTC

Thanks connor for quick response. It solved my purpose. Can you point some good documentation on the multiset,sys.odcinumberlist its use and functionality
Chris Saxon
May 12, 2016 - 2:32 am UTC

sys.odcinumberlist is just a presupplied type. Any 'table of number' type would have done - I'm just too lazy to create one :-)

Multiset is a set operator for object types

http://docs.oracle.com/cd/E11882_01/server.112/e41084/operators006.htm#SQLRF0032


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.