[RESOLVED] Hours between two dates

[boku]

I have a bunch of records in a MySQL database

Typically I would like to work out the amount of hours and minutes between 2 dates for each record in the database...

2010-01-01 00:00:00
2010-01-02 00:00:00

Is 24 hours.

and then add all of those together to get the total amount of hours to the nearest hour.

I am not used fluent in PHP and rarely use it, but now I'm a bit stumped ...

Thanks

[kows]

if this is a MySQL date field, you can use MySQL's DATEDIFF function, and then convert it to a Unix timestamp using MySQL's UNIX_TIMESTAMP in your SELECT so that you can easily manipulate it with PHP. your select might look something like:

SELECT UNIX_TIMESTAMP(DATEDIFF(date1, date2)) as datediff FROM table WHERE ...

then, when you query this in PHP you can access it using the "datediff" key in whatever array you create.

let me know if you need some more help!

edit: oh yeah, and I guess if you don't know already, a Unix timestamp is a date in seconds. so, if you return the Unix timestamp of time between two days, that's the number of seconds between the two dates. you can convert this number to hours by dividing it by 3600 (60 seconds * 60 minutes = 3600 seconds = 1 hour), and then using round() to round the number of hours up if you don't want any decimal places.

edit2: and, if you really wanted, you could also do all of the calculations inside of your MySQL query. it's just simpler for some people to do it outside of the query.

[SambaNeko]

TIMESTAMPDIFF() may work better for you. With it, you can specify that you want the returned difference to be in hours.

[boku]

if this is a MySQL date field, you can use MySQL's DATEDIFF function, and then convert it to a Unix timestamp using MySQL's UNIX_TIMESTAMP in your SELECT so that you can easily manipulate it with PHP. your select might look something like:

SELECT UNIX_TIMESTAMP(DATEDIFF(date1, date2)) as datediff FROM table WHERE ...

then, when you query this in PHP you can access it using the "datediff" key in whatever array you create.

let me know if you need some more help!

edit: oh yeah, and I guess if you don't know already, a Unix timestamp is a date in seconds. so, if you return the Unix timestamp of time between two days, that's the number of seconds between the two dates. you can convert this number to hours by dividing it by 3600 (60 seconds * 60 minutes = 3600 seconds = 1 hour), and then using round() to round the number of hours up if you don't want any decimal places.

edit2: and, if you really wanted, you could also do all of the calculations inside of your MySQL query. it's just simpler for some people to do it outside of the query.

This is what I wrote yesterday

SELECT sum((unix_timestamp(`endtime`) - unix_timestamp(`starttime`)) / 3600) FROM `table` WHERE 1

But I do get decimals, which is annoying ...
Also, yes this is a MySQL database ...

I think I'm going to use this, it looks right ...

SELECT ROUND((UNIX_TIMESTAMP(DATEDIFF(`endtime`, `starttime`)) / 3600)) as datediff FROM `table` WHERE 1

[kows]

er, yeah, I said you had to round it :X I didn't recommend using 'sum.'

[boku]

Thanks Kows :P my bad