Applies to versions: 3.0, 3.1, 3.2

ODBC Access

The TimeIPS® Database Read-Only Access Module allows setting a password to enable read-only access directly to the TimeIPS database. This allows users with database experience to write queries for external applications including custom reporting.

Using the left pane Main Menu » Administration, click on: System » System Settings

Master Configuration

Click on the IPSDBRO Database Configuratoin tab.

1. Type in a password in the "Limited Remote Database Password:" field
   
   
2. Retype the password in the "Limited Remote Database Password (again):" field
   
   
3. Click on the Update Database Configuration button

Database Access

To access the database, you will need an ODBC driver compatible with your operating system.

Visit the MySQL (or MariaDB) web pages for the latest version of a compatible ODBC connector/driver for your operating system. You will be connecting to a 64-bit MySQL/MariaDB server. The SQL you create to process with the ODBC connector will need to be compatible with MariaDB version 10.2.  Documentation on the SQL syntax help is available in many books and web pages.

After the ODBC driver is installed, configure it to communicate with your TimeIPS system's IP address and a database called "timecard." The location of the configuration varies by operating system.

As an example, in Windows XP, access ODBC settings by going to Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC). Click "Add" to add a new data source and select the MySQL/MariaDB driver. Pick a name, like TimeIPS for the connection. Specify the IP address of your TimeIPS System in the Server field. The database name will be '"timecard" and you will use the username "readonly" and the password you configured.

After the ODBC connection is configured, you will need an application that can use ODBC to process queries and/or generate reports. There are many tools available. For example, the free office suite LibreOffice/OpenOffice includes a database component that can be used.

There are many database tables available in TimeIPS. The following list covers some of the main tables with the purpose of the table and notes on using it. The role of tables not listed here are beyond the scope of this document.

Table - Purpose

• users - List of TimeIPS users (employees). The usersID field is used in many other tables to identify the employee. There are over 100 data fields in the users table. The essential fields are:
  
  • usersID - A unique internal database identifier for the employee
  • empNum - The employee alternate number
  • HID - The employee's badge number
  • nameFirst - The employee's first name
  • namePref - The employee's preferred name (nickname)
  • nameMid - The employee's middle name
  • nameLast - The employee's last name
  • mgrID - The usersID of the employee's direct manager
  • username - The username the employee uses to log into TimeIPS
  • password - A hashed password for logging into TimeIPS
• pay_history - Pay history for each user (employee).  Each employee has at least one record in the table to denote their employment.  The begTimestamp denotes the start of employment.  If the endTimestamp is null, the employee is still employed on an ongoing basis.  If the endTimestamp is set, this denotes the end of that segment of their employment.  Additional employment segments are created when an empolyee's title, status or pay changes.  For example, if an employee gets promoted to a new position, a new record will be created in the pay_history table for this change.  Likewise if an employee changes from hourly to salary, or gets a pay raise, goes from part time to full time, etc.  Essential fields are: 
  • usersID - The employee to which the record applies
  • begTimestamp - The start of the employment segment
  • endTimestamp - The end of the employment segment (if NULL this segment does not end)
  • title - The employee's title during this employment segment
  • status - The employee's status during this employment segment
  • isFullTime - If the employee is full time during this segment
  • payRate - The employee's pay rate during this segment
  • payTypeID - The type of pay (hourly/salary) as defined in the pay_types table

• eventLog - Raw and adjusted records of the employee clocks. The usersID field relates to the users table. You will normally use the adjTimeIn and adjTimeOut fields for reporting or calculations. The timeIn and timeOut fields are the actual time the clock was created (i.e. the time the event was created, which could be hours or days before or after the paid time). The realTimeIn and realTimeOut are the time the employee actually clocked (before rounding, snapping, or adjustments) and are used to compare the net effect of adjustments vs. actual. All times are stored as Unix Timestamps (i.e. seconds since midnight, Jan 1st, 1970 in GMT). You will need to convert and process these in your reports. There are over 30 fields in the eventLog table. The essential fields are:
  
  • eventLogID - A unique internal database identifier for the punch
  • usersID - A unique internal database identifier for the employee
  • timeIn - The time the punch-in was created
  • timeOut - The time the punch-out was created
  • adjTimeIn - The paid punch-in time after all adjustments (rounding, snapping, editing)
  • adjTimeOut - The paid punch-out time after all adjustments (rounding, snapping, editing)
  • realTimeIn - The original/intended punch-in time without any all adjustments (rounding, snapping, editing)
  • realTimeOut - The original/intended punch-out time without any adjustments (rounding, snapping, editing)
  • jobID - A unique internal database identifier for the job being worked
  • clockNote - The note left with the clock in
  • clockNoteOut - The note left with the clock out
  • clientIn - A unique internal database identifier for the client that created the punch in
  • clientOut - A unique internal database identifier for the client that created the punch in
  • clientAdjIn - A unique internal database identifier for the client that created the punch in after all adjustments and edits
  • clientAdjOut - A unique internal database identifier for the client that created the punch in after all adjustments and edits
• eventLog_audit - Details on each change made to a time event.  When an event is created, modified or deleted, a new record will be created in this table. This can be very helpful when performing a sync to an external database
  • auditID - A unique internal database identifier for the audit
  • eventLogID - A unique internal database identifier for the punch that was modified, created or deleted
  • eventLog_offlinePendingID - An ID indiciating if the event is offline and pending processing
  • eventLogPart - What was modified, for example "in" "out" or "event"
  • auditTime - Unix timestamp of when the audit change occurred
  • editorID - The usersID of the person who made the change (or NULL if made by the system)
  • source - Where the change was made, for example, "time edit" or "group clock"
  • item - What was done to cause the change, for example "create event" "clock in" "clock out" or "job"
  • detail - Human-readable detail of exactly what happened
• jobs - TimeIPS Jobs. The "code" field holds the code that employees enter to clock into the job.
  • jobID - A unique internal database identifier for the job
  • isActive - 0 or 1, to indicate if the job is active (can be used)
  • lcdName - The small display name (short name) that appears on the clock screen
  • code - The job code, used to clock into the job
  • name - The name of the job
  • ctrlNum - A control number, generally used for billing
  • billingRate - A billing rate for this job to customers
  • catID - A unique internal database identifier for the category this job belongs to
  • custID - A unique internal database identifier for the customer this job is for
  • type - The kind of job, "validated" or "flexible" (almost always validated)
  • glNum - The general ledger code for the job
  • description - The description of the job
  • mgrID - A unique internal database identifier for the usersID of the manager of this job
• zones - Time Zones for employees and clients. When creating reports, use the "zoneid" field in the users table to identify the time zone use when adjusting clock timestamps.
• department - Departments
• user_department_membership - Connecting table for employees to departments
• customers - Customers for Jobs
• payroll_events - Useful for reporting. The payroll_events are processed eventLog events, plus benefits events that are broken into segments for payroll and reporting. This is the table used to produce sign-off reports and payroll exports because it includes the type of pay that is due, such as overtime or doubletime, based on the employees work-weeks and the payroll periods. Please note that this table is NOT updated in real time. It is updated on demand when reports or payroll exports are run.

Please note that the tables sometimes change between versions and the way the data in the tables is used also sometimes changes. If you have critical reports pulling from these tables, we suggest verifying your reports after each TimeIPS upgrade.

Translating Timestamps

Times in TimeIPS are usually stored as Unix Timestamps. Unix timestamps are stored as the number of seconds that have elapsed since the epoch which was midnight Coordinated Universal Time January 1st 1970. This format provides a very accurate and unambiguous record of exactly when an event occurred without the ambiguities caused by time zones or Daylight Saving. It also makes it easy to calculate the duration of an event. For example in the eventLog table the adjusted in and out timestamps can be subtracted to yield the full duration of the event in seconds (adjTimeOut - adjTimeIn).

Timestamps can be somewhat difficult to translate accurately, especially if an employee in question is not following the specific time zone and Daylight Saving rules used by the host TimeIPS server. To that end we have provided our ODBC customers with two functions which allow for easy translation of timestamps.

userDate

Format: userDate(timestamp, usersID, format)

The arguments are as follows

1. The timestamp you want to convert
2. The usersID of the user to use for time zone and DST lookups
3. A format string. Use null for the default format "%m/%d/%Y %h:%i:%s %p" which will translate to something like "04/08/2012 07:27:04 AM". You can construct your own string using the guidelines found here.

Example: SELECT usersID, eventLogID, userDate(adjTimeIn, usersID, null) as "Punch In" FROM eventLog LIMIT 10;

Example: SELECT usersID, eventLogID, userDate(adjTimeIn, usersID, "%W %M %e %Y") as "Punch In" FROM eventLog LIMIT 10;

zoneDate

Format: zoneDate(timestamp, zoneID, format)

The arguments are as follows

1. The timestamp you want to convert
2. The zoneID of the timezone to use for the conversion. This can be found in timecard.zones
3. A format string. This follows the same logic as userDate

Example: SELECT zoneDate(UNIX_TIMESTAMP(), 1, "%m/%d/%Y" ) as "Today";

Example Querys

Below are some example's on how to construct SQL Querys to pull data out of the system with ODBC.

-- Currently active employees and their pay type, title and pay rate. For a time other than the time of the query, put the timestamp in place of both "unix_timestamp()" entries in the query:

SELECT u.usersID,u.nameFirst,u.nameLast,ph.title,pt.name,CONCAT(ph.payRate,' ',pt.salaryBasis) AS pay
FROM users u
JOIN pay_history ph ON ph.usersID=u.usersID
JOIN pay_types pt ON ph.payTypeID = pt.payTypeID
WHERE ph.begTimestamp < unix_timestamp() AND (ph.endTimestamp IS NULL OR ph.endTimestamp > unix_timestamp());

The following query's are for pulling Benefit data out of the database, as benefit time all calculated on the fly, we can't just pull the data out of the table.

-- Used In Range:

SELECT SUM(adjustment) / 3600 as used,usersID,typeID
FROM benefits_logWHERE adjustment < 0
        AND accrualLogID IS NULL
        AND deleted IS NULL
        AND adjusted_on BETWEEN 1363582800 and 1364274000
        AND usersID IN (1)
        AND typeID IN (3)
GROUP BY usersID, typeID;

-- Earned In Range:

SELECT SUM(adjustment) / 3600 as earned,usersID,typeID
FROM benefits_log
WHERE deleted IS NULL
        AND (accrualLogID IS NOT NULL OR (adjusterID IS NOT NULL AND adjustment>0))
        AND adjusted_on BETWEEN 1363582800 AND 1364274000
        AND usersID IN (1)
        AND typeID IN (3)
GROUP BY usersID,typeID;

-- Net In Range:

SELECT u.usersID, tempBenefitLog.typeID, SUM(adjustment) / 3600 as net
FROM users u LEFT JOIN (
    SELECT usersID, typeID, adjustment, adjusted_on
    FROM benefits_log log
    WHERE adjusted_on <= 1364274000
        AND usersID IN (1)
        AND typeID IN (3)
        AND deleted IS NULL
        AND adjustment IS NOT NULL
        AND adjusted_on >= (
            SELECT IFNULL(MAX(occurs),0)
            FROM benefit_reset r
            WHERE r.typeID = log.typeID
                AND r.usersID = log.usersID
                AND r.occurs <= 1364274000
        )

    UNION ALL

    SELECT usersID, typeID, amount as adjustment, occurs as adjusted_on
    FROM benefit_reset r
    WHERE resetID NOT IN (
            SELECT r1.resetID
            FROM benefit_reset r1, benefit_reset r2
            WHERE r1.typeID = r2.typeID
                AND r1.usersID = r2.usersID
                AND r1.occurs <= 1364274000
                AND r2.occurs <= 1364274000
                AND r1.occurs < r2.occurs
                AND r1.typeID IN (3)
                AND r1.usersID IN (1)
        )        
AND r.usersID IN (1) AND r.typeID IN (3) AND r.occurs <= 1364274000
) as tempBenefitLog ON u.usersID = tempBenefitLog.usersID
WHERE u.usersID IN (1)
GROUP BY tempBenefitLog.usersID, tempBenefitLog.typeID;

-- Available At Start

SELECT u.usersID, tempBenefitLog.typeID, SUM(adjustment) / 3600 as net
FROM users u LEFT JOIN (
    SELECT usersID, typeID, adjustment, adjusted_on
    FROM benefits_log log
    WHERE adjusted_on <= 1363582800
        AND usersID IN (1)
        AND typeID IN (3)
        AND deleted IS NULL
        AND adjustment IS NOT NULL
        AND adjusted_on >= (
            SELECT IFNULL(MAX(occurs),0)
            FROM benefit_reset r
            WHERE r.typeID = log.typeID
                AND r.usersID = log.usersID
                AND r.occurs <= 1363582800
        )

    UNION ALL

    SELECT usersID, typeID, amount as adjustment, occurs as adjusted_on
    FROM benefit_reset r    WHERE resetID NOT IN (
            SELECT r1.resetID
            FROM benefit_reset r1, benefit_reset r2
            WHERE r1.typeID = r2.typeID
                AND r1.usersID = r2.usersID
                AND r1.occurs <= 1363582800
                AND r2.occurs <= 1363582800
                AND r1.occurs < r2.occurs
                AND r1.typeID IN (3)
                AND r1.usersID IN (1)
        ) AND r.usersID IN (1) AND r.typeID IN (3) AND r.occurs <= 1363582800
) as tempBenefitLog ON u.usersID = tempBenefitLog.usersID
WHERE u.usersID IN (1)
GROUP BY tempBenefitLog.usersID, tempBenefitLog.typeID;