Solved

Calculate Working Hours in interval

Posted on 2010-11-23
7
1,081 Views
Last Modified: 2012-05-10
Hello Experts,

I have a Postgres DB  with some tables with columns of timestamp type without time zone
like: Creation_Date_UTC  and Closure_date_UTC
ie: 2010-11-05 07:42:00
2010-11-05 18:54:00

What I want is maybe a function able to calculate "Working Hours" [09:00:00 to 18:00:00] from the interval in those two columns.

In this function I should  also be able to specify the "working hours" interval, that the function will use for calculation. The result will be the number of working hours between the two dates [columns] calculated based on specified working hours interval.

ie: get_working_hours(Creation_Date_UTC, Closure_date_UTC, Work_Period_start, Work_Period_end)

ie: get_working_hours(2010-11-05 10:00:00, 2010-11-05 17:00:00, 09:00:00, 18:00:00)

In this case the result should be 7. [hours]

My date format in Postgres is DD/MM/YYYY and time format 24h.

Thanks so much!
0
Comment
Question by:daniel_spiri
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 34202798
See this thread to inform yourself

http://www.experts-exchange.com/Database/Oracle/Q_21449885.html

This is a complicated problem and one you need to define the requirements for weekends, religious festivals, bank holidays, shift patterns, lunch breaks, dentists appointments etc.

ps You can simply subtract timestamp from the other to get the interval...

a simple approach if Closure date and creation dates are on the same day...
select min(date_trunc('day',Closure_date_UTC)  + Work_Period_end::time , Closure_date_UTC)  -
max(date_trunc('day',Creation_Date_UTC) + Work_Period_start::time, Creation_Date_UTC)
as working_hours from your_query;
0
 

Author Comment

by:daniel_spiri
ID: 34204073
Hey Earthman,
Thanks for the pointers!
I had those in mind, but right now, I don't worry about weekends, religious festivals, shift patterns etc. Although those features would be nice, I know they are more difficult and need dedicated professional time.

In my case I will have an interval of Monday to Friday to calculate on.

Can your script be used like that?
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 34215913
I don't know if your problem is how to use plpgsql or how to construct the programming logic but here is a framework to develop,

test1=> create table xi( id int, Creation_Date_UTC timestamp, Closure_date_UTC timestamp );
CREATE TABLE
test1=> insert into xi values ( 1, now(), now() + '9 hours'::interval + '12 minutes'::interval );
INSERT 0 1
test1=> select * from xi;
 id |     creation_date_utc      |      closure_date_utc      
----+----------------------------+----------------------------
  1 | 2010-11-26 07:49:23.570259 | 2010-11-26 17:01:23.570259
(1 row)

test1=> create or replace function working_hours( start_ts timestamp, end_ts timestamp, wp_start time, wp_end time ) returns interval as $$
test1$> declare
test1$>  x interval;
test1$>  nod integer;
test1$> begin
test1$>   nod := cast(date_trunc('day', end_ts) as date) - cast( date_trunc('day',start_ts) as date);
test1$>   if nod = 0 then
test1$>     x := least(date_trunc('day',end_ts)  + wp_end, end_ts)  -
test1$>          greatest(date_trunc('day',start_ts) + wp_start, start_ts);
test1$>   end if;
test1$>
test1$>   return x;
test1$> end;
test1$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
test1=> select working_hours( Creation_Date_UTC,Closure_date_UTC,'09:00:00'::time,'17:00:00'::time) from xi;
 working_hours
---------------
 08:00:00
(1 row)

test1=>
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Closing Comment

by:daniel_spiri
ID: 34217877
Thanks again Earthman!
0
 

Author Comment

by:daniel_spiri
ID: 34217941
These are some of the results I am receiving after calling the function:

2010-11-07 17:09:00      2010-11-08 04:18:00      NULL
2010-11-05 08:23:00      2010-11-08 08:06:00      NULL
2010-11-05 08:26:00      2010-11-05 08:27:00      -00:33:00

2010-11-05 09:44:00      2010-11-08 08:19:00      NULL
2010-11-05 10:50:00      2010-11-05 13:13:00      02:23:00
2010-11-05 12:22:00      2010-11-05 20:56:00      05:38:00

2010-11-05 01:14:00      2010-11-05 01:19:00      -07:41:00
2010-11-05 02:49:00      2010-11-05 03:05:00      -05:55:00
2010-11-05 05:47:00      2010-11-05 07:58:00      -01:02:00

I think it's working good, but are the "minus" results supposed to be there?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 34231884
test1=> create or replace function working_hours( start_ts timestamp, end_ts timestamp, wp_start time, wp_end time ) returns interval as $$
test1$> declare
test1$>   x interval := interval '0 seconds';
test1$>   d1 date := date_trunc('day', start_ts);
test1$>   d2 date := date_trunc('day', end_ts);
test1$>   x1 timestamp := greatest(d1 + wp_start, start_ts);
test1$>   x2 timestamp := least(d2 + wp_end, end_ts);
test1$>   wd interval;
test1$>   dow integer := date_part( 'dow', d2 );
test1$> begin
test1$>   if ( dow = 1 and end_ts < (d2 + wp_start) ) then
test1$>     d2 := d2 - 3;
test1$>     x2 := d2 + wp_end;
test1$>   elsif ( dow = 0 ) then
test1$>     d2 := d2 - 2;
test1$>     x2 := d2 + wp_end;
test1$>   elsif ( dow = 6 ) then
test1$>     d2 := d2 - 1;
test1$>     x2 := d2 + wp_end;
test1$>   end if;
test1$>   dow := date_part( 'dow', x1 );
test1$>   if ( dow = 5 and start_ts > (d1 + wp_end) ) then
test1$>     d1 := d1 + 3;
test1$>     x1 := d1 + wp_start;
test1$>   elsif ( dow = 0 ) then
test1$>     d1 := d1 + 1;
test1$>     x1 := d1 + wp_start;
test1$>   elsif ( dow = 6 ) then
test1$>     d1 := d1 + 2;
test1$>     x1 := d1 + wp_start;
test1$>   end if;
test1$>
test1$>   if d1 = d2 and dow > 0 and dow < 6 then
test1$>     if ( x2 > x1 ) then
test1$>       x := x2 - x1;
test1$>     end if;
test1$>   else
test1$>     wd := wp_end - wp_start;
test1$>     if ( x2 > x1 ) then
test1$>       if ( (d1+wp_end) > x1 ) then
test1$>         x := (d1+wp_end) - x1;
test1$>       end if;
test1$>       d1 := d1 + 1;
test1$>       while ( d1 < d2 ) loop
test1$>         dow := date_part( 'dow', d1 );
test1$>         if ( dow > 0 and dow < 6 ) then
test1$>           x := x + wd;
test1$>         end if;
test1$>         d1 := d1 + 1;
test1$>       end loop;
test1$>       if ( d1 <> d2 and (d2+wp_start) < x2 ) then
test1$>         x := x + ( x2 - (d2 + wp_start));
test1$>       end if;
test1$>     end if;
test1$>   end if;
test1$>   return x;
test1$> end;
test1$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
test1=> create or replace function working_hours( start_ts timestamp, end_ts timestamp, wp_start time, wselect id,Creation_Date_UTC,Closure_Date_UTC, working_hours( Creation_Date_UTC,Closure_Daee_UTC,'09:00:00'::time,'17:00:00'::time) from xi;
 id |     creation_date_utc      |      closure_date_utc      | working_hours
----+----------------------------+----------------------------+---------------
  1 | 2010-11-26 07:49:23.570259 | 2010-11-26 17:01:23.570259 | 08:00:00
  2 | 2010-11-07 17:09:00        | 2010-11-08 04:18:00        | 00:00:00
  3 | 2010-11-05 08:23:00        | 2010-11-08 08:06:00        | 08:00:00
  4 | 2010-11-05 08:26:00        | 2010-11-05 08:27:00        | 00:00:00
  5 | 2010-11-05 09:44:00        | 2010-11-08 08:19:00        | 07:16:00
  6 | 2010-11-05 10:50:00        | 2010-11-05 13:13:00        | 02:23:00
  7 | 2010-11-05 12:22:00        | 2010-11-05 20:56:00        | 04:38:00
  8 | 2010-11-05 01:14:00        | 2010-11-05 01:19:00        | 00:00:00
  9 | 2010-11-05 02:49:00        | 2010-11-05 03:05:00        | 00:00:00
 10 | 2010-11-05 05:47:00        | 2010-11-05 07:58:00        | 00:00:00
(10 rows)

test1=>
0
 

Author Comment

by:daniel_spiri
ID: 34290608
This is great Earthman, thanks alot!
I've run some tests and found that sometimes it seems to return incorrect results.

working_hours("Start_date_UTC", "Restoration_date_UTC", '05:00:00'::time without time zone, '16:00:00'::time without time zone)

Restoration_date_UTC      Start_date_UTC      working_hours      actual_result      Differences
02.05.2009 10:55         01.04.2009 10:19      236:41:00           247,6833333         10,98333333
08.01.2010 06:59         03.04.2009 07:28      2197:32:00         2199,516667         2,016666667
30.04.2009 09:50         06.04.2009 11:12      191:48:00           196,6333333         4,833333333

The results from actual_result column are from a VBA function we are using to calculate working hours. Based on the Differences column we can see that the sql workin_hours function is skipping a few hours. Where's the error?

Again, thank you very much for your help Earthman!
I am grateful!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now