PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

ms accesspostgresqltimestamp

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:

non aggregate query results

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

e

It results in:

Aggregate query

PostgreSQL gives 47 which is definitely not the sum. Access gives 47.8702777776634.

Any ideas?

Best Answer

I gave up on making the ODBC driver work in access to calculate this sum properly.

My solution was to create a view in PostgreSQL which calculated the sum of the hours. See below:

    SELECT employees.empid,
    employees.startdate,
    employees.payrate,
    employees.surname,
    employees.first_name,
    emphours.weekending,
    emphours.department,
    emphours.paytype,
    sum(date_part('epoch'::text, (emphours.endtime - emphours.starttime) / 3600::double precision)) AS hours,
    sum(date_part('epoch'::text, (emphours.endtime - emphours.starttime) / 3600::double precision)) * employees.payrate AS totalpay
   FROM employees
     JOIN emphours ON employees.empid = emphours.empid
  GROUP BY employees.empid, employees.startdate, employees.payrate, employees.surname, employees.first_name, emphours.weekending, emphours.department, emphours.paytype;

Although I needed to change some access queries slightly, this seemed to be an effective workaround.

I also considered creating pass though queries but it seemed to be more work than creating the view.

Related Question