0

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'?

5
Contributors
8
Replies
10
Views
6 Years
Discussion Span
Last Post by smantscheff
0

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!

0

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,

0

What about using the following query?

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

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;
}
0

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;
}
0

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       |
+----------+----------+---------+-----------------+-----------------+
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.