0

I am working on a web app and need to total a field an array of records.

To further explain please look at the included image:

enter image description here

This image depicts my output from using the following query:

SELECT * FROM logbook WHERE dayNight = 'D'

Now what I need to do is total all the values in the hours field of all these records.

Once this is done, the output should be something of the following (just as an example):

6:05:21

Representing HH:MM:SS.

But I would like to represent it in just hours and mins, in a decimal value... So if I had 6 hours and 40 mins and 2 seconds, it would say jsut 6:40. This would be the following structure HH:MM.

So I don't know how to do any of this - but thank you for all the help!

Improve this question
6
  • The dayNight field contains the letter D. How is adding the letter D going to result in a time? You must mean some other column. Please clarify that and tag your question with what RDBMS we're supposed to help you with. Commented Aug 14, 2011 at 4:52
  • Now that we know you want to add the values in the hours column, please share what data type this column is. It looks like a string, while strings aren't easily added. You still need to tag the question with what RDBMS you're using. The answer will depend on that. Commented Aug 14, 2011 at 4:56
  • I have fixed this for you - you are right I did mean to say: the hours field. And I am using mySQL with php. Commented Aug 14, 2011 at 4:56
  • The data type for hours is varChar, yeah string Commented Aug 14, 2011 at 4:57
  • That's bad. Can you change it to something that makes more sense? If not, you will have to explain how to parse it. What does "01.00.09 hours" mean? Commented Aug 14, 2011 at 4:58

1 Answer 1

Reset to default
3

You can most of the way with a whole bunch of nasty casting:

select cast(sum(cast(hours as time)) as time)
from logbook
where dayNight = 'D'

That will give you a result as a time value. Then mix in a time_format to get the desired precision:

select time_format(cast(sum(cast(hours as time)) as time), '%H:%i')
from logbook
where dayNight = 'D'

You should change your hours column from varchar to time.

You can get away with just this:

select time_format(sum(cast(hours as time)), '%H:%i')
from logbook
where dayNight = 'D'

If you want to avoid the extra casting; however, leaving the outer casting in might make things easier to debug later as just sum(cast(hours as time) produces things like 234211 for 23:42:11.

Once your columns have been converted to sensible time columns, then you can do away with all the casting:

select time_format(sum(hours), '%H:%i')
from logbook
where dayNight = 'D'

or to sum the all regardless of dayNight:

select time_format(sum(hours), '%H:%i')
from logbook
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

So thanks heaps for this, I am getting bad responses though: benjaminpotter.org/elog/iframe/index.php
I have converted all the hours field to a time data type! So how should I go about this now? Also how would I get the total time no matter the dayNight value? - Again thanks heaps @mu is too short for the help!
@Ben: See my update please. I had a typo in one of the queries from last night too, that's been fixed.
That does look better, although I am getting this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from logbook LIMIT 0, 30' at line 1
"select time_format(sum(hours), '%H:%i') from times from logbook where dayNight = 'D' "
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.