INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How do I format seconds into hours and minutes?

How do I format seconds into hours and minutes?

(OP)
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.

RE: How do I format seconds into hours and minutes?

This feels about right (sorry, not tested)

CODE

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close