Time tricks

Alpha Five does not have a time data type, but by using the time manipulation functions, totime() and toseconds(), you can do just about any kind of time addition or subtraction. We'll illustrate with a simple time card application, in which we'll record time_in, time_out, break_time, and hourly_rate, and we'll calculate the elapsed time and the total billing.

The time functions

These are the two built-in Alpha Five functions we will use:

toseconds(character_value) - takes a value such as "11:00 am" and returns the number of seconds since midnight. The toseconds() function is smart enough to understand 24-hour time as well as conventional am/pm designation. If given 24-hr time input, the toseconds() function is not limited to only 24 hours, but can return the seconds equivalent of longer time spans.

totime(number_value, format_code,decimal_places) - converts seconds into hours, minutes, and, depending on the format specified, days, seconds, and thousandths of a second.

Next, let's create a table to contain the fields for our time card application:

The total_time field is a calculated field that will store the elapsed time between time_int and time_out, subtracting the break_time.
The total_time field is an example of a calculated field. You establish the calculated field rule Field Rules, Type:

Calculated fields are found under Field Rules, Type.

The actual calculated field expression for total_time is
TOTIME(TOSECONDS(TIME_OUT)-TOSECONDS(TIME_IN)-TOSECONDS(BREAK_TIME),1,2)
The amount field is another calculated field. Its value is simply
RATE*TOSECONDS(TOTAL_TIME)/3600

It helps to click on the Expression Builder button (the little button labeled x-y) when entering calculated fields.

If you now enter some values in the default
form for the table, you can see how the time card works:


You can enter times as either 13:00 or 1:00 pm or even 1:00 p. Alpha Five doesn't care.
We can improve on this time card by allowing for night shift workers. The current calculation for total_time only works if time_out>time_in.
If someone shows up at 11 pm and leaves at 7 am, the current calculation gives a negative value for total_time! This is clearly going to lead to a union grievance! We can fix this problem by checking to see if time_out is earlier than time_in. If so, we just add 24 hours to the result of the previous total_time calculation to take into account the change in day. The new calculated field rule for total_time then becomes

CASE(TOSECONDS(TIME_OUT)>TOSECONDS(TIME_IN),TOTIME
(TOSECONDS(TIME_OUT)-TOSECONDS(TIME_IN)-TOSECONDS(BREAK_TIME),
1,2),.T.,TOTIME(TOSECONDS(TIME_OUT)+24*3600-
TOSECONDS(TIME_IN)-TOSECONDS(BREAK_TIME),1,2))
This is all one expression on a single line. It is shown on several lines so it will fit onto your browser screen. Let's check this expression:

The new expression gives the correct calculation for night shift workers.

This long nested CASE statement is a lot to type in the expression editor, and it is not very clear. It would certainly be clearer as a user defined global function. Why don't you write one?

6/13/98 - pkw

Return to home page