Enabling Date/Time Calculations on GE Security's Picture Perfect™ PPSQL Reports
Updated 9/29/2003
Background
Often, we want to be able to make computations based on dates and times of various events logged in the GE Security's (formerly CASI-RUSCO) Picture Perfect database. Some of the types of things we'd like to compute are:
- Number of days since a badge was used
- List of badge holders whose last access is more than a specified number of days
- Average number of transactions per day between two dates
We also want to be able to display dates in a more readable format than the YYYYMMDD format that Picture Perfect uses. A convenient procedure that solves both problems is MDY_PROC(). This is a stored procedure that is installed as part of the Picture Perfect base package. MDY_PROC() converts a date in YYYYMMDD format to US English MM/DD/YYYY format. DMY_PROC() converts a date from YYYYMMDD format to European DD/MM/YYYY format. The output of these procedures can then be used in computations involving the Informix TODAY variable.
Here are some simple examples that demonstrate the use of MDY_PROC().
Example: List all of the readers accessed by a specified employee within the last 10 days.
SELECT reader_desc, MDY_PROC(xact_date) FROM badge_history
WHERE MDY_PROC(xact_date) >= TODAY - 10 AND employee = {Employee}
Example: Show all badges that haven't been used in a specified number of days sorted with most recently used badges first.
SELECT bid, employee, MDY_PROC(access_date), access_date, reader.description
FROM badge, reader
WHERE badge.reader = reader.description
AND MDY_PROC(access_date) < TODAY - {Number of Days Since Last Access}
ORDER BY 4 DESC
If you need better accuracy or if you need to make calculations between points in time, you can use the PPDATETIME() procedure provided at the end of this page. This procedure merges Picture Perfect dates (YYYYMMDD) and times (HHMMSS) into an Informix DATETIME data type which can be used for computations, is sortable, and is internationalized.
Example: Show all transactions by employee WILLIAMS within the last 4 days in European date format.
SELECT DMY_PROC (xact_date) DATE, HMS_PROC (xact_time) TIME,
b.initials, bhc.last_name[1,15], bhc.reader_desc
FROM badge_history bhc, badge b
WHERE bhc.last_name matches "WILLIAMS"
AND bhc.bid = b.bid
AND PPDATETIME (bhc.xact_date, bhc.xact_time)
>= CURRENT - INTERVAL (4) DAY TO DAY
ORDER BY 4 ASC, 3 ASC, 1 DESC, 2 DESC
The output looks like this:
| DATE | TIME | INITIALS | LAST_NAME | READER_DESC |
| 02/09/2003 | 18:52:49 | GW | WILLIAMS | 135200 SEAWTC Q4 FOYER DBL DR |
| 02/09/2003 | 18:25:28 | GW | WILLIAMS | 1726200 PHI5/200L FREIGHT DBL |
| 02/09/2003 | 18:03:44 | GW | WILLIAMS | 177101 SEA4/82D TEST FACILITY |
| 02/09/2003 | 17:43:37 | GW | WILLIAMS | 117200 SEAWTC STE75 RES PLUS |
| 02/09/2003 | 17:25:28 | GW | WILLIAMS | 175201 OR03/1B W HALL SGL DRS |
| 02/09/2003 | 17:09:27 | GW | WILLIAMS | 268100 FW01/1G S LOBBY GLASS |
| 02/09/2003 | 16:53:57 | GW | WILLIAMS | 2034201 DC/100M FRNT SIDE DR |
| 02/09/2003 | 16:40:25 | GW | WILLIAMS | 2134201 DC3/100C E LBY ENT 2 |
The same report in US English format using MDY_PROC() would look like this:
| DATE | TIME | INITIALS | LAST_NAME | READER_DESC |
| 09/02/2003 | 18:52:49 | GW | WILLIAMS | 135200 SEAWTC Q4 FOYER DBL DR |
| 09/02/2003 | 18:25:28 | GW | WILLIAMS | 1726200 PHI5/200L FREIGHT DBL |
| 09/02/2003 | 18:03:44 | GW | WILLIAMS | 177101 SEA4/82D TEST FACILITY |
| 09/02/2003 | 17:43:37 | GW | WILLIAMS | 117200 SEAWTC STE75 RES PLUS |
| 09/02/2003 | 17:25:28 | GW | WILLIAMS | 175201 OR03/1B W HALL SGL DRS |
| 09/02/2003 | 17:09:27 | GW | WILLIAMS | 268100 FW01/1G S LOBBY GLASS |
| 09/02/2003 | 16:53:57 | GW | WILLIAMS | 2034201 DC/100M FRNT SIDE DR |
| 09/02/2003 | 16:40:25 | GW | WILLIAMS | 2134201 DC3/100C E LBY ENT 2 |
Example: List all of the badges that haven't been used for access within a specified number of days, sorted in decreasing chronological order:
SELECT bid, PPDATETIME(access_date, access_time) ACCESSED,
reader.description READER FROM badge, reader
WHERE badge.reader = reader.id
AND PPDATETIME(access_date, access_time) > CURRENT - INTERVAL ({Number of Days}) DAY (3) TO DAY
ORDER BY 2 DESC
That example produces this output:
| BID | ACCESSED | READER |
| 0003614515 | 2003-07-23 11:07:20 | 011100 SEA4/260F LBY DR (BTRY) |
| 0003215264 | 2003-07-20 17:57:09 | 007101 SEA10/260F LOBBY DOOR |
| 0003210822 | 2003-07-18 15:54:28 | 007100 SEA10/260F COMPUTER RM |
Example: Use PPDATETIME and the badge history's tzcorr field to display today's badge history events in local time.
SELECT
PPDATETIME(xact_date, xact_time) SYSTEM_TIME,
tzcorr CORR,
PPDATETIME(xact_date, xact_time) + tzcorr UNITS HOUR LOCAL_TIME,
reader_desc READER,
bid,
last_name LAST
FROM badge_history
WHERE MDY_PROC(xact_date) = TODAY
ORDER BY 1
| SYSTEM_TIME | CORR | LOCAL_TIME | READER | BID | LAST |
| 2003-09-02 01:28:14 | -3 | 2003-09-01 22:28:14 | 272400 OR03/1C EMER STR DR | 0003210510 | GODIN |
| 2003-09-02 01:29:21 | -3 | 2003-09-01 22:29:21 | 272201 OR03/1B FIRSCO ISS LAB | 0003209565 | BUTLER |
Installation
To install the PPDATETIME() stored procedure, follow these steps.
Copy the following snippet of code into your Windows clipboard:
#----------------- begin ---------------------------
#include "disclaimer.h"
install_procs()
#
##########################################################
{
#
# Add procedures that allow a YYYYMMDD and HHMMSS to be converted to a
# single expression that can be evaluated as a DATE or DATETIME value.
#
sqlstmt 'DROP PROCEDURE HMS_PROC' >>$LOG 2>&1
sqlstmt 'DROP PROCEDURE PPDATE' >>$LOG 2>&1
sqlstmt 'DROP PROCEDURE PPTIME' >>$LOG 2>&1
sqlstmt 'DROP PROCEDURE PPDATETIME' >>$LOG 2>&1
sqlstmt 'CREATE PROCEDURE HMS_PROC (time_data char (6)) RETURNING char(8);
DEFINE p_time_data char(6);
DEFINE n_time_data char(8);
DEFINE p_temp_hour int;
DEFINE p_temp_min int;
DEFINE p_temp_sec int;
IF (time_data IS NULL) THEN
LET n_time_data = "00:00:00";
ELSE
IF (LENGTH (time_data) = 1) THEN
LET p_time_data = "00000" || time_data[1,1];
ELIF (LENGTH (time_data) = 2) THEN
LET p_time_data = "0000" || time_data[1,2];
ELIF (LENGTH (time_data) = 3) THEN
LET p_time_data = "000" || time_data[1,3];
ELIF (LENGTH (time_data) = 4) THEN
LET p_time_data = "00" || time_data[1,4];
ELIF (LENGTH (time_data) = 5) THEN
LET p_time_data = "0" || time_data[1,5];
ELSE
LET p_time_data = time_data[1,6];
END IF
LET n_time_data = p_time_data[1,2] || ":" || p_time_data[3,4] || ":" || p_time_data[5,6];
END IF
RETURN n_time_data;
END PROCEDURE' >>$LOG 2>&1
sqlstmt 'CREATE PROCEDURE PPDATE (date_data char (8)) RETURNING DATE;
DEFINE p_date DATE;
DEFINE p_year INT;
DEFINE p_mon INT;
DEFINE p_day INT;
LET p_year = date_data[1,4];
LET p_mon = date_data[5,6];
LET p_day = date_data[7,8];
LET p_date = MDY (p_mon, p_day, p_year);
RETURN (p_date);
END PROCEDURE' >>$LOG 2>&1
sqlstmt 'CREATE PROCEDURE PPTIME (time_data CHAR (6)) RETURNING INTERVAL HOUR TO SECOND;
DEFINE p_time_data CHAR(8);
DEFINE p_time_int INTERVAL HOUR TO SECOND;
IF (time_data IS NULL) THEN
LET p_time_int = "00:00:00";
ELSE
LET p_time_data = "000000";
IF (LENGTH (time_data) = 1) THEN
LET p_time_data = "00000" || time_data[1,1];
ELIF (LENGTH (time_data) = 2) THEN
LET p_time_data = "0000" || time_data[1,2];
ELIF (LENGTH (time_data) = 3) THEN
LET p_time_data = "000" || time_data[1,3];
ELIF (LENGTH (time_data) = 4) THEN
LET p_time_data = "00" || time_data[1,4];
ELIF (LENGTH (time_data) = 5) THEN
LET p_time_data = "0" || time_data[1,5];
ELSE
LET p_time_data = time_data[1,6];
END IF
LET p_time_int = p_time_data[1,2] || ":" || p_time_data[3,4] || ":" || p_time_data[5,6];
END IF
RETURN p_time_int;
END PROCEDURE' >>$LOG 2>&1
sqlstmt 'CREATE PROCEDURE PPDATETIME (p_ppdate CHAR(8), p_pptime CHAR(6)) RETURNING DATETIME YEAR TO SECOND;
DEFINE p_ppdatetime DATETIME YEAR TO SECOND;
DEFINE p_date DATE;
DEFINE p_interval INTERVAL HOUR TO SECOND;
CALL PPDATE (p_ppdate) RETURNING p_date;
CALL PPTIME (p_pptime) RETURNING p_interval;
LET p_ppdatetime = EXTEND (p_date, YEAR TO SECOND) + p_interval;
RETURN p_ppdatetime;
END PROCEDURE' >>$LOG 2>&1
}
LOG=/tmp/r.log
. /cas/bin/profile
install_procs
#------------------- end ------------------------------
Use your favorite telnet or ssh client to establish a session on the Picture Perfect host.
Switch users to root and change to the /tmp directory.
Type:
cat >datetime.sh
Then, paste the contents of the Windows clipboard to the terminal session window.
Press Enter once.
Hold down the CTL key and press D (CTL-D).
Type:
sh datetime.sh
The result should be that the PPDATETIME(), PPDATE(), PPTIME(), and HMS_PROC() routines will be installed as stored procedures. You can verify that they were successfully installed by typing the following command:
selectit 'SELECT procid FROM sysprocedures WHERE procname = "ppdatetime"'
If the procedure has been installed, an integer will be displayed.
