[EMAIL PROTECTED] wrote:
Christopher Molnar <[EMAIL PROTECTED]> wrote on 06/27/2005
12:55:08 PM:
<snip>
What I would like to use is:
select ctype, count(tsid), sum(amount), sum(amount_collected), sum
(camount), sum(commision), sum(subtime(time_out,dispatch_time)) as
time from time_sheet where tdate='2005-06-22' group by ctype;
NOTE: This adds a SUM() to the time column.
Is this do-able - (doesn't work this way - maybe in another manner?
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
What else would you have it do? The problem, I think, is the need to do date
math, not the storage method.
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).
That won't work very well. FROM_UNIXTIME AND UNIX_TIMESTAMP work with DATEs.
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(SUBTIME('14:00','12:00')));
+---------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(SUBTIME('14:00','12:00'))) |
+---------------------------------------------------------+
| 1970-01-01 00:00:00 |
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
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.)
Yes, this is the way to go.
mysql> SELECT SEC_TO_TIME(TIME_TO_SEC(SUBTIME('14:00','12:00')));
+----------------------------------------------------+
| SEC_TO_TIME(TIME_TO_SEC(SUBTIME('14:00','12:00'))) |
+----------------------------------------------------+
| 02:00:00 |
+----------------------------------------------------+
1 row in set (0.00 sec)
Thanks.
-Chris
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;
Not this.
-- 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;
Yes, this.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
