mysql time calculations

[lifeworks]

Hi

I have a table storing time-sheet data, including a start & end time. I've set those fields as 'TIME' format, and tried doing the following calculation

SELECT SUM(log_end - log_start) AS timetotal FROM time_logs WHERE log_client = 'ECDC'

It returned an integer, which doesn't correspond with any of my other calculations... is this integer 'number of seconds'?

[debasisdas]

Test using some sample data you will come to know.

[lifeworks]

OK, after some fiddling Ive worked out that if the difference is say 44 minutes, then i get a return of 4400... which I assume means 44mins and 0 seconds...

I then padded the string using str_pad($string,6,'0',STR_PAD_LEFT), but I get values like 01:90:33

Which isnt right either...? more help please!

[almostbob]

read the php date() function in the online manual, let me google that for you to output the machine readable number to any human readable text format you want
see also time()

[lifeworks]

I think I worked out one of the problems, the mysql 'end-start' returns an integer, and because i was trying to get a total amount, i end up summing the integers and destroying their inherent time values...

i may leave the adding to my php script,

[mwasif]

What about using the following query?

SELECT log_end - log_start AS timetotal FROM time_logs WHERE log_client = 'ECDC'

[lifeworks]

Ive decided to go with my trusty php script which i got form someone else,

i pull the log_start,log_end from my database, it arrives in the format 09:56:00 (9:56 am), and i plug these two values into the PHP below and get the seconds value back (a bit like a unix time stamp). It doesnt work for times captured across multiple days, fortunately not many people in the office pull all nighters.

function get_time_total($start,$end){
	$bits_s = explode(":",$start);
	$bits_e = explode(":",$end);

	$start_mktime = mktime($bits_s[0],$bits_s[1],$bits_s[2],0,0,0);
	$end_mktime = mktime($bits_e[0],$bits_e[1],$bits_e[2],0,0,0);

	$diff_mktime = $end_mktime - $start_mktime;

	return $diff_mktime;
}

[lifeworks]

Ive decided to go with my trusty php script which i got form someone else,

i pull the log_start,log_end from my database, it arrives in the format 09:56:00 (9:56 am), and i plug these two values into the PHP below and get the seconds value back (a bit like a unix time stamp). It doesnt work for times captured across multiple days, fortunately not many people in the office pull all nighters.

function get_time_total($start,$end){
	$bits_s = explode(":",$start);
	$bits_e = explode(":",$end);

	$start_mktime = mktime($bits_s[0],$bits_s[1],$bits_s[2],0,0,0);
	$end_mktime = mktime($bits_e[0],$bits_e[1],$bits_e[2],0,0,0);

	$diff_mktime = $end_mktime - $start_mktime;

	return $diff_mktime;
}

[smantscheff]

I deem it bad practice to do calculations in PHP which can be done on the database level. It is inefficient (because of the database in- and output) and it's error prone if the data are accessed through an other interface than PHP.

Have a look at the time_diff function:

drop table if exists t;
create table t (t1 time, t2 time);
insert into t values ('00:01:00', '00:02:00');
select t1, t2, t2 - t1, timediff(t2,t1), timediff(t1,t2) from t;
+----------+----------+---------+-----------------+-----------------+
| t1       | t2       | t2 - t1 | timediff(t2,t1) | timediff(t1,t2) |
+----------+----------+---------+-----------------+-----------------+
| 00:01:00 | 00:02:00 |     100 | 00:01:00        | -00:01:00       |
+----------+----------+---------+-----------------+-----------------+