Time difference in hours 2

[humbleprogrammer]

Hello,

I would like to display how many hours have elapsed between 2 times. The times are stored in the MySQL database as time data types. I have been able to calculate the difference in seconds but haven't had any luck converting that to hours. Below is what I am currently using:

$time_difference = (gmmktime($start_time)-gmmktime($end_time));

For a start time of 12:00:00 and a end time of 05:00:00 this is returning 25200. How can I convert this into hours so it would display 5?

Thanks in advance!

 

[sleipnir214]

First of all, you need to look at the PHP online manual page for what gmmktime() takes as input (

http://php.he.net/manual/en/function.gmmktime.php).

It does not take a single value, but rather a list of values.

Second, gmmktime returns a number of seconds. To get hours, divide by 3600 (60 seconds/minute * 60 minutes/hour).


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[csniffer]

well thats a strange one because there is only 18000 seconds between the two, i think that something else is wrong

To err is human, to completely mess up takes a computer.

 

[DRJ478]

What about having MySQL do the work and using the MySQL date/time functions and perform the operation in the query?
Read:

http://www.mysql.com/doc/en/Date_and_time_functions.html

 

[humbleprogrammer]

Hi DRJ478,

This would be ideal but unfortunately the TIME, TIMESTAMP and TIMEDIFF functions are only supported in MySQL 4.1.1 and the version the clients site is running on is 3.23.

The fields in the database are saved as time data types. I need to display the duration between the start_time and end_time like:

Event Start Time: 2:00 (3 hours)

The above would be grabbing a start_time of 02:00:00 and a end_time of 05:00:00 from the table, which would display the 3 hours.

Can someone provide some sample code on how to accomplish this? I am at a loss.

Thanks!

 

[sleipnir214]

I'm not sure what "grabbing a start_time of 02:00:00 and a end_time of 05:00:00 from the table" means.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[csniffer]

right if you already know how to get the seconds as u said in the first post. 5hrs = 60s x 60m x 5h = 18000. so if you new that u had 18000 seconds then the answer is simple. just do like sleipnir214 said 2days ago and that was divide 18000 by 60 then divide the answer by 60 again, this then brings 18000/60 = 300 , 300/60 = 5 ok is that any clearer.


To err is human, to completely mess up takes a computer.

 

[humbleprogrammer]

Well, I thought I was getting the correct seconds but as you pointed out in your earlier post, it wasn't returning the correct seconds. The gmmktime I was using wasn't giving me what I needed. If I knew how to get the seconds, I would be able to take it from there with sleipnir214's tip but I haven't been able to get the correct seconds.

What I meant by "grabbing a start_time of 02:00:00 and a end_time of 05:00:00 from the table", is I am querying the table and the results are returning the start_time of 02:00:00 and a end_time of 05:00:00. Sorry for the lingo.

 

[csniffer]

is it possible for u to supply some relevant commented code that u have already done? maybe some one can see an error and then are better able to help u. this way you are more likely to get help as people can see that you dont want the code writing 4 u.

To err is human, to completely mess up takes a computer.

 

[danielhozac]

The following assumes that

start_date

is in

$start_date

and

end_date

is in

$end_date

.

$start_date_arr = explode(":", $start_date);
$end_date_arr = explode(":", $end_date);
$start_date_time = gmmktime($start_date_arr[0], $start_date_arr[1], $start_date_arr[2]);
$end_date_time = gmmktime($end_date_arr[0], $end_date_arr[1], $end_date_arr[2]);
$difference = $end_date_time - $start_date_time;
$difference /= 3600;

//Daniel

 

[humbleprogrammer]

csniffer - see my first post. That is what I was trying to use to output the seconds and it is returning 25200. I could be wrong but after reading the PHP manual on gmmktime, I was lead to believe this works with datetime data types but not time data types. I assure you that I don't want code written for me. This is a very small piece of a big project. If I wanted code written for me I would be here posting about the entire project rather then just a time difference issue.

Daniel - Thanks for the response. Your sample is outputting -7 for $difference /= 3600;
and
25200 for $difference = $end_date_time - $start_date_time;

This is exactly the problem I have been having. No matter what I try it's not treating the fields like time fields but instead it's treating them as numbers.

See below for more details on how the fields are being displayed:

$start_time=$row[start_time]; //returns 12:00:00 from the database
$end_time=$row[end_time]; //returns 05:00:00 from the database
$second_difference = (gmmktime($start_time)-gmmktime($end_time)); //returns 25200
$difference = $second_difference/60/60; //returns 7

 

[skiflyer]

Well, you're getting the proper output, you just don't realize it

The difference between noon and 5 am is 7 or -7 depending on how you ask the question.

And 12:00:00 is certainly noon
and 05:00:00 is certainly 5 am

Perhaps you're trying to ask the difference between noon and 5 pm, which is 12:00:00 and 17:00:00.

Good luck.

-Rob

 

[skiflyer]

Sorry, but to be a bit more helpful, if you want the positive difference between the end and the start, it should always be

end_time - start_time... time moves forward.

-Rob

 

[sleipnir214]

humbleprogrammer:

Please review my first post in this thread. You are not passing the correct values into gmmktime().

Please examine danielhozac's example scriptlet carefully. You will see he's not passing gmmktime() the entire D/T string as you are in your string. He's passing the function the correct input values.

Please read the PHP online manual page:

http://us2.php.net/manual/en/function.gmmktime.php

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[DRJ478]

I think you did give up on the SQL solution too fast.
MySQL does wonderful work subtracting 'time' fields.
Let's say you have the values you describe:
starttime: 05:00:00
endtime : 12:00:00

SELECT endtime-starttime FROM myTable WHERE 1;

yields: 70000 -> which translates to 7 hours 0 minutes 0 seconds.

I perceive absolutely no need to invoke any mktime functions to solve this problem.

 

[humbleprogrammer]

Thanks Rob. This make sense. I believe my problem is the time isn't being stored as military time. Is there a simple function that converts civilian time to military time? If not I can write my own. Anyway, I think this will put me on the right track.

Thanks again!!!

 

[DRJ478]

humbleprogrammer
The MySQL data type TIME stores time values in hours, minutes, seconds. It has no concept of a.m. or p.m. since just values are stored.
It is coincidental that 'military time' looks like the entries in the time data type. However, this data type can hold values from -838:59:59 to 838:59:59 which starts to look very much unlike military time.

To store timestamps you should consider datetime or timestamp values and work with those.

 

[humbleprogrammer]

Thanks DRJ478! I will do this.