title: Postgresql: How to find hours between 2 dates and 2 times?
body: <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>