Postgresql: How to find hours between 2 dates and 2 times?
<p>Postgresql 9.1</p>

<p>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. </p>

<p>I've looked at the <a href="http://www.postgresql.org/docs/9.1/static/functions-datetime.html" rel="nofollow">date time docs for 9.1</a> and still haven't found what I need. </p>

<ul>
<li>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. </li>
<li>I could not find out to convert a date and time to some thing else to use another function. </li>
<li>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. </li>
<li>NOTE: I don't think I can add user-defined functions. I don't have the permissions. </li>
</ul>

<p>Example data: </p>

<ul>
<li>Startdate and time: '2016-04-29' and '23:00:00'</li>
<li>Enddate and time: '2016-04-30' and '01:30:00'</li>
</ul>

<p>I've also tried this sql statement. </p>

<pre><code>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 &gt;= '2016-04-24') 
       AND (nonchargeabletime.startdate &lt;= '2016-04-30') 
       AND (employeetime.dcmasterid IN ('BLURG')) 
       AND (nonchargeabletime.nonchargeabletype=10) 
ORDER BY employeetime.dcmasterid, nonchargeabletime.startdate, nonchargeabletime.starttime;
</code></pre>

<p>But I get a syntax error at <code>startdate</code> where it says <code>(timestamp (startdate ||</code>.</p>

<p>Anyone have any clues how to do this? </p>

<p>Thank you.</p>