[SOLVED] MySQL date math

[simon551]

 

should be in mySQL forum sorry. if you know how to move then please do.

 

Hi,

 

I have this query to sum timesheet entries in my application. The user can enter hours and/or minutes and then we calculate the value. My question is about whether I can change the value back to hours:minutes for printing.

 

This is my query right now:

SELECT timesheet_entries.id, Sum(COALESCE(timesheet_entries.hours,0))  + Sum(COALESCE(timesheet_entries.minutes,0)/60) AS Rtotal FROM timesheet_entries  GROUP BY timesheet_entries.id

 

It takes the hours and the minutes and combines them to output a number like 3.5 for 3 hours 30 minutes. Is there a way to break that number back down and convert it to at least look like 3:30. Some users, in testing, are getting confused by the output of 3.5 after entering 3 hours and 30 minutes (which is annoying but I digress.)

[lightningstrike]

Edit: Mistake.

[simon551]

I'm not sure either.

 

What you posted prints 07:30

 

I would like 3.5 to print as 3:30 (or 03:30 would be okay too).

 

Thanks!

-s

[lightningstrike]

lol i wasn't thinking clearly when i wrote that nvm.

[Wildbug]

In SQL:

mysql> SELECT CONCAT(FLOOR(3.5),' hours, ',(3.5 * 60) % 60, ' minutes');                               
+-----------------------------------------------------------+
| CONCAT(FLOOR(3.5),' hours, ',(3.5 * 60) % 60, ' minutes') |
+-----------------------------------------------------------+
| 3 hours, 30.0 minutes                                     | 
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(FLOOR(3.5),':',LPAD(TRUNCATE((3.5 * 60) % 60,0),2,'0'));                                     
+----------------------------------------------------+
| CONCAT(FLOOR(3.5),':',TRUNCATE((3.5 * 60) % 60,0)) |
+----------------------------------------------------+
| 3:30                                               | 
+----------------------------------------------------+
1 row in set (0.01 sec)

[simon551]

awesome!

 

thank you wildbug!