PHP/MySQL date-time processing - help please!
PHP/MySQL date-time processing - help please!
Author
Discussion

TheExcession

Original Poster:

11,669 posts

281 months

Wednesday 6th June 2007
quotequote all
ok, I'm in need of a bit of assistence here.... this date-time stuff really confuses me!

I'm building an SQL query in PHP that needs to search a table but only for the records within a certain age.

Every record in the table has a timestamp - the column is of MySQL type DATETIME.

So assuming I have this piece of information in PHP

$TimeStamp = "2007-06-06 16:42:36"

How do I prepare a query string that will pick up everything within the last five seconds?

Such as....

Select * from events WHERE date_time >= ???? AND blah blah blah

where ???? would be '2007-06-06 16:42:31'

I guess I need a PHP function to do the date subtraction.... or can it be done in the SQL better?

Any offers?

Many thanks
Ex






cottonfoo

6,053 posts

241 months

Wednesday 6th June 2007
quotequote all
select * from events where datefield >= date_sub(curdate(), interval 5 second);

TheExcession

Original Poster:

11,669 posts

281 months

Wednesday 6th June 2007
quotequote all
cottonfoo said:
select * from events where datefield >= date_sub(curdate(), interval 5 second);
Thanks fella, I'd seen a few examples like that but not got them to work.

I think I finally found it in PHP too

$now_seconds = strtotime ($time_stamp);
$now_seconds_minus_5_seconds = $now_seconds - ( 5 );
$new_date = date ("Y-m-d H:i:s", $now_seconds_minus_5_seconds);
echo "New Date:" . $new_date . "<br />";


Gives me:
TimeStamp:2007-06-06 17:35:14
New Date :2007-06-06 17:35:09

Which I think is what I'm looking for.

best
Ex