Thanks! Worked perfectly. And also thanks to the other people who
responded. Being unfamiliar with how MySQL seems to handle time your
responses really helped!
-Chris
On Jun 27, 2005, at 1:16 PM, [EMAIL PROTECTED] wrote:
That would have worked if TIME values weren't converted to numbers
by packing their components together. (This is one of my very few
peeves with MySQL. Packing works well for sorting but not at all
for date math). To get good date math working, you need to use the
FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert your time
differences into and out of actual numeric values (ones that are SUM
()-able).
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
You could also use time_to_sec() and sec_to_time() to do the same
down-conversion/restoration. (Any pair of functions that convert
the a time into some kind of numeric value and back will work but
these come to mind first.)
Try this:
SELECT ctype
, count(tsid)
, sum(amount)
, sum(amount_collected)
, sum(camount)
, sum(commision)
, FROM_UNIXTIME(sum(UNIX_TIMESTAMP(subtime
(time_out,dispatch_time)))) as time
FROM time_sheet
WHERE tdate='2005-06-22'
GROUP BY ctype;
-- or --
SELECT ctype
, count(tsid)
, sum(amount)
, sum(amount_collected)
, sum(camount)
, sum(commision)
, SEC_TO_TIME(sum(TIME_TO_SEC(subtime
(time_out,dispatch_time)))) as time
FROM time_sheet
WHERE tdate='2005-06-22'
GROUP BY ctype;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
