Time is one of the parameters observed in a research environment and often, there is a need to calculate for the average time. Using the average function directly on the time field will create an erroneous result. Time field needs to be in decimal number format to calculate for the average time correctly.

EXAMPLE DATABASE: Training4

DATA INPUT

Table varietypeak from the Training4.mdb database is the input.

QUERY time2decimal

SELECT variety, group, day, 
      (time_rep1*24) AS rep1, 
      (time_rep2*24) AS rep2, 
      (time_rep3*24) AS rep3
FROM varietypeak;

Query time2decimal converts the time field into decimal number format.

DISCUSSION

The time field can be converted directly into a decimal number format by simply multiplying the time field by 24, i.e. time_value x 24. As a result, if a given time is 10:30, the output decimal number format is 10.5, since 30 minutes is 0.50 fraction of an hour.

The following are the time equivalents for a fraction of an hour:

QUERY daily_variety_avg

SELECT variety, group, 
       round(avg(rep1),2) AS avg_rep1, 
       round(avg(rep2),2) AS avg_rep2, 
       round(avg(rep3),2) AS avg_rep3
FROM time2decimal
GROUP BY variety, group;

Query daily_variety_avg calculate a plant's average peak time of fertility for 3 days. This was taken after time values have been converted to decimal format.

DISCUSSION

The average peak time was calculated using the AVG( ) aggregate function GROUPed BY variety and group. The results are still in decimal format, thus giving us the hour value and the fraction of an hour value for the minute. An example is the average decimal value 10.75 which is equivalent to a time of 10:45 since 0.75 fraction of an hour is 45 minutes.

Now, there is a need to convert the decimal format back into time format. It was illustrated here