Time calculation in query

[Wylie C]

I have a numeric field that I am using to convert to hours and minutes. If
the number is 2631070 and I use the following sql query it returns 583 hours
and 70 minutes. Don't think I should have 70 seconds....What have I done
wrong?

SELECT Sum(Statistics.TSeconds) AS SumOfTSeconds,
Sum(Format([Tseconds]\3600,"00")) AS [Hours Ridden], Format(Sum([TSeconds]
Mod 3600\60),"00") AS [Minutes Ridden]
FROM Statistics;

 

[Rob Parker]

Well, cutting and pasting your sql statement and applying it to a table
containing a single record of 2631070 seconds gives an answer of 730 hours
and 10 minutes. Closer than your 583 hours and a bit, but no cigar ;-) -
the correct answer is 730 hours, 51 minutes (and 10 seconds).

No idea where your 583 hours comes from - unless it was a typo in your
posting!

Getting the minutes correct requires (TSeconds Mod 3600)\60, rather than
TSeconds Mod 3600\60 - this evaluates as TSeconds Mod 60, which is the
remaining seconds.

If you haven't noticed, your hours will not contain a leading zero (if less
than 10), because you're applying the Format statement before the hours are
calculated.

Here's an SQL statement which will give you total seconds, and hours,
minutes and seconds, with leading zeros on minutes and seconds only:

SELECT Sum(Statistics.TSeconds) AS SumOfTSeconds,
Sum([Tseconds])\3600 AS [Hours Ridden],
Format(Sum([TSeconds] Mod 3600)\60,"00") AS [Minutes Ridden],
Format(Sum([Tseconds]) Mod 60,"00") AS [Seconds Ridden]
FROM Statistics;

HTH,

Rob

 

[MGFoster]

I have a numeric field that I am using to convert to hours and minutes. If
the number is 2631070 and I use the following sql query it returns 583 hours
and 70 minutes. Don't think I should have 70 seconds....What have I done
wrong?

SELECT Sum(Statistics.TSeconds) AS SumOfTSeconds,
Sum(Format([Tseconds]\3600,"00")) AS [Hours Ridden], Format(Sum([TSeconds]
Mod 3600\60),"00") AS [Minutes Ridden]
FROM Statistics;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you'll have better luck putting the Format & division outside
the Sum() function:

SELECT Sum(TSeconds) AS SumOfTSeconds,

Format( SUM(Tseconds) \ 3600, "00") AS [Hours Ridden],

Format( ( SUM(TSeconds) Mod 3600 ) \ 60, "00") AS [Minutes Ridden]

FROM Statistics;

========================================

My calculations (debug window):

? 2631070 \ 3600
730
? (2631070 mod 3600) \ 60
51

The integer divisor has precedence over the mod operator, therefore, put
the mod calculation in parentheses.

Why are you using the Format() function on the result? It is usually a
good idea to let the display function format the result. Down the road
you may wish to use this query as the source of another query and want
to perform a mathematical function on the Hours/Mins. If they are
Formatted, the numbers will be strings & you will have to translate the
string numbers back to numerics.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQigS34echKqOuFEgEQKpzgCdFXl0QZXLv4xKwveHco8fUyag96YAn1qb
SQXyJkJkca8UvAQ5Lz5mlh2u
=Rsrj
-----END PGP SIGNATURE-----