How do I format seconds into hours and minutes?

[kzn]

Hi I need to format seconds into hours and minutes.

This is my query so far .... there is probably a way easier way of doing it, I am so close yet so far ... any help appreciated thank you

select time_format(sec_to_time(sum(time_to_sec(timediff(endTime,startTime)))),'%h %i') as total,date(dateTimeStamp) as date,account from timesheet where user_id = 13 and (date(dateTimeStamp) > '2013-10-01' and date(dateTimeStamp) < '2013-10-31') group by date,account;

This is the result ... I have replaced the account name with a *.
+-------+------------+----------+
| total | date | account |
+-------+------------+----------+
| 01 10 | 2013-10-09 | * |
| 04 40 | 2013-10-09 | * |
| 02 15 | 2013-10-10 | * |
| 12 45 | 2013-10-10 | * |
| 02 50 | 2013-10-10 | * |
| 12 30 | 2013-10-11 | * |
| 02 30 | 2013-10-11 | * |
| 02 20 | 2013-10-14 | * |
| 03 30 | 2013-10-14 | * |
| 05 50 | 2013-10-15 | * |
| 05 10 | 2013-10-16 | * |
| 04 15 | 2013-10-17 | * |
| 01 35 | 2013-10-17 | * |
| 01 20 | 2013-10-18 | * |
| 04 30 | 2013-10-18 | * |
| 05 50 | 2013-10-21 | * |
| 12 45 | 2013-10-22 | * |
| 05 05 | 2013-10-22 | * |
| 05 20 | 2013-10-23 | * |
| 12 30 | 2013-10-23 | * |
| 04 40 | 2013-10-24 | * |
| 01 10 | 2013-10-24 | * |
| 05 05 | 2013-10-25 | * |
| 05 45 | 2013-10-28 | * |
| 04 35 | 2013-10-29 | * |
| 12 55 | 2013-10-29 | * |
| 12 15 | 2013-10-29 | * |
| 02 00 | 2013-10-30 | * |
| 03 50 | 2013-10-30 | * |
| 04 00 | 2013-10-30 | * |
+-------+------------+----------+

The total results starting with 12 are incorrect because the seconds values are less than an hour. Here is the result seconds.
+-------+------------+
| total | date |
+-------+------------+
| 4200 | 2013-10-09 |
| 16800 | 2013-10-09 |
| 8100 | 2013-10-10 |
| 2700 | 2013-10-10 |
| 10200 | 2013-10-10 |
| 1800 | 2013-10-11 |
| 9000 | 2013-10-11 |
| 8400 | 2013-10-14 |
| 12600 | 2013-10-14 |
| 21000 | 2013-10-15 |
| 18600 | 2013-10-16 |
| 15300 | 2013-10-17 |
| 5700 | 2013-10-17 |
| 4800 | 2013-10-18 |
| 16200 | 2013-10-18 |
| 21000 | 2013-10-21 |
| 2700 | 2013-10-22 |
| 18300 | 2013-10-22 |
| 19200 | 2013-10-23 |
| 1800 | 2013-10-23 |
| 16800 | 2013-10-24 |
| 4200 | 2013-10-24 |
| 18300 | 2013-10-25 |
| 20700 | 2013-10-28 |
| 16500 | 2013-10-29 |
| 3300 | 2013-10-29 |
| 900 | 2013-10-29 |
| 7200 | 2013-10-30 |
| 13800 | 2013-10-30 |
| 14400 | 2013-10-30 |
+-------+------------+



This is my timesheet table structure
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------------------+----------------+
| timesheet_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| activity_id | int(10) | NO | | NULL | |
| account | varchar(60) | YES | | NULL | |
| so | int(10) | YES | | NULL | |
| startTime | timestamp | NO | | 0000-00-00 00:00:00 | |
| endTime | timestamp | NO | | 0000-00-00 00:00:00 | |
| comments | text | YES | | NULL | |
| dateTimeStamp | datetime | NO | | NULL | |
| setincomplete | char(1) | YES | | n | |
+---------------+------------------+------+-----+---------------------+----------------+

I am trying to analyse a specific users daily input. Group tasks in each day that relate to an account and total up hours and minutes for each account.


I hope what I have put is clear enough ... any help appreciated.

 

[jpadie]

This feels about right (sorry, not tested)

SELECT 	CAST(
		SUM(
			timediff(endTime, startTime)
		)
		AS TIME) as total,
		CAST( dateTimeStamp AS DATE ) as `date`,
		account 
FROM	timesheet 
WHERE 	user_id = 13 
	AND	
	CAST(dateTimeStamp AS DATE) BETWEEN CAST('2013-10-01' AS DATE) AND CAST('2013-10-31' AS DATE) 
GROUP BY
	CAST(dateTimeStamp AS DATE) ASC,
	account