Many databases include dates that define significant periods of time and deserve analysis. Court data can have arrest date, trial date and sentence date, for example. Inspection data can have date reported, date inspected and date enforcement action was taken.
Database software has the capability to do calendar arithmetic to answer questions such as, "Which case went the longest between arrest and trial?" or "Which inspection cases took the longest to get around to?"
Sometimes, however, hours and minutes count. For example, you might want to calculate response times for emergency calls, phone call duration, or how long someone was in jail. Comparing the elapsed time with this information is a little more involved, since the beginning time may start at 2:03 p.m. on one day and the ending time may be at 5 a.m. the next morning or the next week.
This month's tip is a custom FoxPro function that you can use to do these time calculations. You can download the file TimeDat.zip from http://www.reporter.org
The file contains the source code for the functions, a sample table to test with, instructions on how to use the file and the functions.
The functions are: DateTime(), DateTimeDiff(), and Hours(). You can use these functions as you would the regular built-in FoxPro functions: in a query, or in a FoxPro command such as REPLACE ALL. Copy the file TimeDat.prg to your FoxPro directory and then in the command window, type:
set procedure to TimeDat.prg
This makes the functions available. If you exit FoxPro or issue the CLOSE ALL command, you will have to type the command again to use the functions. For more information, see the documentation file included in TimeDat.zip
For illustration, I'll assume a table called TEST (included in TimeDat.zip) with five columns: ID, BeginDate, BeginTime, EndDate and EndTime. The question we're trying to answer is how much time passed between one starting date and time and the ending date and time.
The function DateTime takes two arguments: a date and a clock time. The clock time must be in the 24-hour format and indicated in hh:mm:ss or in hh:mm. The function converts the date and clock time to a single unit to define a point in time: the Julian day and the decimal proportion of the time of that day. If you want to think of Julian date as "star date," that's OK, since it is used in astronomy to sidestep the quirks of our calendar.
The function DateTimeDiff compares two DateTime results and returns the time interval, expressed in days. To convert the interval to hours, divide the result by 24.
Here are some query examples. This one uses DateTime: SELECT id, ; DateTime (begindate, begintime) ; DateTime (enddate, endtime) ; FROM test
This next query adds one more output column, using to calculate the days elapsed between beginning time/date and the ending time/date. Note that the functions are nested: the values returned from DateTime are passed to DateTimeDiff:
SELECT id, ; DateTime (begindate, begintime) ; DateTime (enddate, endtime) ; DateTimeDiff (DateTime (begindate, begintime) ; DateTime (enddate, endtime) ) ; FROM test
Here is how the DateTime function works, applying it to June 16, 1996, at 15:46:31.
The Julian date for 06/16/96 is 2450251. A Julian date is just a really big number: 2,450,251. It doesn't break down into anything else. At 15:46:31 on the clock, the day is 15.7753 hours old. Dividing that by 24 shows what proportion of the day has passed: 0.6573. Adding the Julian day and the proportion of the incomplete day gives the absolute reference we need to measure time periods that span a calendar day. With this kind of measure we can do time/date arithmetic on the mayor's cell phone calls. Here is what the FoxPro command window would look like to get these answers. The SYS(11) function is a built-in FoxPro function that converts a calendar date to a Julian date. FoxPro has more than 50 different SYS() functions (In the commands below, the lines in bold would show up on the screen, not in the command window).
Richard Mullins can be reached at (573) 882-2127, or send e-mail to richard@nicar.org