best way to find the diff between to time values

edmaunders

Hello;
I have a problem which im not sure how to solve.

Im trying to do build a to do list were a job must be fin 15mins before the job complete time.

it a table called job_done i have a feild called jobfin which takes the system time of the users once they click the job done button, i also have another feild call time_complete.

Now what is the best to do this... work out the diff between the two times with PHP and store the value in the database and just query it for all records that are above the 15 mins

Or using a SQL query to work out the diff and display the records

I would love any help on this because at the moment i have no idea were to start

many thanks
ed

shamly

One way is to convert the date/time entered to timestamp value. You can do this using mktime() function. mktime returns the Unix timestamp corresponding to the arguments given which is in seconds.

You can store this timestamp value in the DB. 15 minutes in seconds is 900.

edmaunders

Ok cool so once i have the unix timestamp how do i find out the diff?

thanks

shamly

ok so lets assume you set the job finish time to 10:45 on 14-Apr-2009.
The job is completed at 10:20 on 14-Apr-2009.

Then job deadline in timestamp is

$deadline = mktime(10, 45, 0, 4, 14, 2009)

Job finish timestamp is

$finish_time = mktime(10, 20, 0, 4, 14, 2009)

now $deadline - $finish_time should give you a value greater than or equal to 900 (15 minutes).

edmaunders

thanks for u quick replay

finish and deadline time are stored in a table as time fields as HH:MM:SS i dont no how to split them up so they can be used in the mktime function as mktime(hh,mm)

thanks
ed

NogDog

SELECT * FROM job_done WHERE time_complete - INTERVAL 15 MINUTE >= jobfin

bradgrafelman

You could use either MySQL's Date and Time Functions (assuming you're using MySQL), or you could just use [man]explode/man in PHP to split up the timestamp at every ':'.

EDIT: Nog posted while I was typing, which is good, since he must've actually taken the time to read through the thread and think of a better way to go about it (something I didn't do :o)!

edmaunders

hey thank you all so much,

can some please explaine the query that Nog posted as i fairly new to this

many thanks
ed

NogDog

It's just subtracting 15 minutes from the "time_complete" value and making sure that that time is >= the "jobfin" time.

edmaunders

ok this may be to much to ask but i really dont understand sorry,

ok i have a table called jobdone

job name Varchar
Stime Time
deadline Time

Ok so for example i want to query the database and find result were job were started less than 15 min,

Job name clean up
Stime 12:12:00
deadline 12:20:00

here you can see that the above job was started on 8 mins before the deadline. How could i use that query above

i know this is a big ask but ur help is amazing thanks so much
Ed

NogDog

If you want to find jobs that were started less than 15 minutes from the deadline, then you would reverse the comparison.

SELECT * FROM `jobdone` WHERE `deadline` - INTERVAL 15 MINUTE < `Stime`

edmaunders

SELECT * FROM `test` WHERE `deadline` - INTERVAL 15 MINUTE < `Stime`

When i uses the query above it dose not return any results, im not sure if i have my databse set up right. both deadline and Stime are set as time feilds 00:00:00

Im sorry for keep going on at you but i really need this working thanks

shamly

Can you pls paste the exact table structure and sample inserted data.

edmaunders

I think i sloved it.. it seemed to work..

The feilds Stime and deadling were set as Time. but i changed them to datetime values and it seemed to work

SELECT * from test WHERE date_add( Stime, INTERVAL 15 MINUTE) > `deadline`

I have only tested it with six records but it displayed the correct result.. im going to start to add a large number of test data to night and see it works

let me know what you think..

Thanks agian for all your help i could not of done it with out you guys
ED