Postgresql: How to find hours between 2 dates and 2 times?

2
votes

Postgresql 9.1

I'm trying to use a query to find the difference, in fractional/decimal hours, between 2 dates and 2 times for a timesheet system. I'm using the query to make sure the software (not written by me) doesn't have any bugs. Here are the fields in the table I'm using: startdate is a Date field, starttime is a Time field, enddate is a Date field, endtime is a Time field.

I've looked at the date time docs for 9.1 and still haven't found what I need.

  • age() takes 2 timestamps and appears to give a difference in integer, not fractional, days. I don't think it's possible to multiply the result of age() by 24 to get hours. Nor do I know how to include the time in the age() function.
  • I could not find out to convert a date and time to some thing else to use another function.
  • I have searched Google and Stackoverflow and have not found info to help me. I've been spending about 4 weeks on this off and on. So probably 30 hours already.
  • NOTE: I don't think I can add user-defined functions. I don't have the permissions.

Example data:

  • Startdate and time: '2016-04-29' and '23:00:00'
  • Enddate and time: '2016-04-30' and '01:30:00'

I've also tried this sql statement. 

SELECT employeetime.dcmasterid as empid, 
       nonchargeabletime.startdate as ncsdate, 
       nonchargeabletime.starttime as ncstime, 
       nonchargeabletime.enddate as ncedate, 
       nonchargeabletime.endtime as ncetime, 
       employeetime.dchours as normhrs, 
       (timestamp (startdate || ' ' || starttime) - timestamp (enddate || ' ' || endtime)) as diffhrs  
FROM employeetime, nonchargeabletime 
WHERE (nonchargeabletime.employeetime=employeetime.dcautoinc) 
       AND  (nonchargeabletime.startdate >= '2016-04-24') 
       AND (nonchargeabletime.startdate <= '2016-04-30') 
       AND (employeetime.dcmasterid IN ('BLURG')) 
       AND (nonchargeabletime.nonchargeabletype=10) 
ORDER BY employeetime.dcmasterid, nonchargeabletime.startdate, nonchargeabletime.starttime;

But I get a syntax error at startdate where it says (timestamp (startdate ||.

Anyone have any clues how to do this?

Thank you.

3
postgresqldatedatetimepostgresql-9.1
Why are you not using a single timestamp column? – a_horse_with_no_name
I didn't design the database. That's what the vendor did. This is mostly a black box system. The fact that I got read access to the db is a minor miracle in itself. – Bulrush
And : you dont need string concatanation to combine date+time: (z.startdate+z.starttime)::timestamp will give you the combined values @a_horse_with_no_name : Ok! – wildplasser
@wildplasser: you don't even need the cast: (enddate + endtime) - (startdate + starttime) will return an interval – a_horse_with_no_name
@wildplasser: Thanks. My calculated field 'diffhrs' is now returning hours and minutes in this format : 08:50:00. How do I convert that to decimal hours? Is there a single function to convert that to decimal hours? – Bulrush

3 Answers

10
votes

Adding a time to a date yields a timestamp and subtracting one timestamp from another returns an interval.

So all you need to do is:

(enddate + endtime) - (startdate + starttime) as diff

An interval is nice in the context of SQL, but usually harder to handle in a programming language. You can easily convert an interval to seconds using extract(epoch from interval)

If you want to convert that to hours use extract and divide by 3600

extract(epoch from (enddate + endtime) - (startdate + starttime))/3600 as diff_hours
1
votes

Since you don't have strings, you can't use || operator, but you can just add time to date (http://www.postgresql.org/docs/9.1/static/functions-datetime.html).

This should work (you can floor result if you want integer hours):

postgres=# create temporary table ts (startdate date, starttime time, enddate date, endtime time);
CREATE TABLE
postgres=# insert into ts values('2016-05-03', '11:45:15', '2016-05-04', '13:55:43');
INSERT 0 1
postgres=# SELECT startdate,starttime,enddate,endtime, (enddate+endtime)-(startdate+starttime) as interval from ts;
 startdate  | starttime |  enddate   | endtime  |    interval    
------------+-----------+------------+----------+----------------
 2016-05-03 | 11:45:15  | 2016-05-04 | 13:55:43 | 1 day 02:10:28
(1 row)

postgres=# SELECT startdate,starttime,enddate,endtime, EXTRACT(epoch FROM ((enddate+endtime)-(startdate+starttime)))/3600 as hours from ts;
 startdate  | starttime |  enddate   | endtime  |      hours       
------------+-----------+------------+----------+------------------
 2016-05-03 | 11:45:15  | 2016-05-04 | 13:55:43 | 26.1744444444444
(1 row)
0
votes
WITH zooi(startdate,starttime, enddate,endtime) AS (
    VALUES('2016-04-29'::date , '23:00:00'::time
        ,'2016-04-30'::date , '01:30:00'::time )
        )
, stamps (sta, sto) AS (
        select (z.startdate+z.starttime)::timestamp
        , (z.enddate+z.endtime)::timestamp
        FROM zooi z
        )
SELECT sta,sto
        , age(sto,sta) AS how_old
        , (sto-sta)::time AS diff
FROM stamps;

Next step would be to convert the time (or interval) result to days or hours.