Solved

Calculate Working Hours in interval

Posted on 2010-11-23
7
1,085 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert bulk records in fact table 2 427
Latest columnn value in SQL or PostgreSQL 2 191
Postgresql  SQL output set column width 3 269
Easy to use inventory management software 7 92
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 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