After the calculation for the average time was done in time's equivalent decimal format, the result needs to be converted back into the standard time format.

EXAMPLE DATABASE: Training4

DATA INPUT

The query daily_variety_avg from the Training4.mdb database is the input.

QUERY decimal2time

SELECT variety, group, 
      TIMEVALUE(DATEPART("h",avg_rep1/24) & ":" & DATEPART("n",avg_rep1/24) & ":" & DATEPART("s",avg_rep1/24)) AS avg_time_rep1,       
      TIMEVALUE(DATEPART("h",avg_rep2/24) & ":" & DATEPART("n",avg_rep2/24) & ":" & DATEPART("s",avg_rep2/24)) AS avg_time_rep2, 
      TIMEVALUE(DATEPART("h",avg_rep3/24) & ":" & DATEPART("n",avg_rep3/24) & ":" & DATEPART("s",avg_rep3/24)) AS avg_time_rep3
FROM daily_variety_avg; 

Query decimal2time outputs the calculated average time in decimal format back into the standard time format.

DISCUSSION

1. To start the conversion, divide the calculated daily average per replicate by 24, avg_rep#/24.

2. Then, extract the hour part using the DATEPART("h",<time_in_decimal>) phrase, DATEPART("h",avg_rep#/24).

3. To extract the minute part, use the DATEPART("n",<time_in_decimal>) phrase, DATEPART("n",avg_rep#/24). When the minute part is extracted, its value stays with the current minute value even if the value for the seconds part is closer to the next minute value.

4. To extract the seconds part, use the DATEPART("s",<time_in_decimal>) phrase, DATEPART("s",avg_rep#/24).

5. The ampersand (&) symbols concatenate the hour part, the minute part, the seconds part, and the colons (:).

6. To return the results in time format, the TIMEVALUE( ) function is used. The TIMEVALUE(<expression>) function converts a string, to hh:mm:ss Date/Time format.

As a result, the average time in decimal format 10.75 will be converted back into time format which is 10:45:00.