I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well but I have one problem.
We have a time clock program where a user clocks in at one time and clocks out at another. The row in the database records this start time and end time. The data looks like this:
The query looks like this:
SELECT emphours.id, emphours.empid, emphours.department, emphours.weekending, emphours.paytype, emphours.starttime, emphours.endtime, ([endtime]-[starttime])*24 AS hours FROM emphours;
The starttime field and the endtime field are both timestamps without timezone in PostgreSQL. Up to this point all is good.
If I try to make an aggregate query to sum all the hours in a week for a given employee and pay type, I lose the precision in the hours. It not only rounds it but It truncates the decimal. Take the following query for example:
SELECT emphours.empid, emphours.department, emphours.weekending, emphours.paytype, Sum(([endtime]-[starttime])) AS hours FROM emphours GROUP BY emphours.empid, emphours.department, emphours.weekending, emphours.paytyp
It results in:
PostgreSQL gives 47 which is definitely not the sum. Access gives 47.8702777776634.