php+mysql timestamp question

Discussion in 'OT Technology' started by crotchfruit, Jul 3, 2003.

  1. crotchfruit

    crotchfruit Guest

    ok, i've got a table named 'test':

    create table test (data text, time timestamp);

    now, at any given time from a php script, i want to delete all entries in 'test' that are 10 minutes older than what the php function 'date("YmdHis");' returns.

    so, in pseudosql it would be something like this??

    (php) $curtime = date("YmdHis");
    (->mysql) delete from test where time < ($curtime - 10 minutes)

    is there a simple way to do this?

    thanks :) :bowdown:
  2. Astro

    Astro Code Monkey

    Mar 18, 2000
    Likes Received:
    Cleveland Ohio
    You have to do some tweaking and you have two approaches: tweak via PHP code or tweak via SQL

    By PHP:

    You will need to take the time and add 10 minutes to it then reformat it to MySQL's timestamp format (which you already listed). One trick you can do is use one of my favorite PHP funtions: strtotime(). Strtotime() takes a date/time string and creates a Unix timestamp. This time stamp is in seconds so its a matter of adding (10 * 60 * 60) to it. I believe something like this might work:

    // $cutoffdate will equal the time to query on in your SQL statement
    $cutoffdate date('YmdHis'strtotime($yourtimestring) + (10 3600));
    I think that will work. There's plenty of other ways to do this too, like mktime() might work better for you. The cool part is these functions already take into account rollovers to the next day, month, and/or year.

    The other option is SQL:

    'SELECT * FROM mytable WHERE mytime < (YEAR(NOW()) + MONTH(NOW) + DAY(NOW()) + HOUR(NOW()) + MINUTE(NOW() + 10) + SECONDS(NOW())'
    The only trick here is if the minutes roll over and create a funky time stamp, will the query still work? I'm not 100% sure that will work (and I'm not 100% that query will work - I may have botched the function names - check out for specifics). You might also look into DATE_FORMAT() - thats a very handy function, but I'm not sure if it will be of much help (if anything, it will take whatever you cooked up and allow you to format it to meet the time stamp needs - think of it like PHP's date() function).

    Those are just some ideas. I do use the above PHP code to do some tricks on expiring web accounts. Although I don't use strtotime() in this case:

    $time date('YmdHis'time() - ($expireday 60 60 24));

Share This Page