I got a php script where a user will set something to happen at a future date. A unix time stamp is created and stored in mysql. A cron job hits a php script every 15 minutes to see what future dates are coming up and executes them.

Problem is I can't seem to figure out the best way to do this because if I do something like:

SELECT * FROM cron WHERE TIME > UNIX_TIMESTAMP( )

This will show ALL the ones that are in the future, how can I narrow this down more so there isn't much of a gap, like say within a 5 minute time difference. Because the options of future posts are in 15 increments (0,15,45)...so a user can set something that they want to post on 7:45 or 9:15.

SELECT * FROM cron where ((time - UNIX_TIMESTAMP()) < 600) > 0

I would thing the above sql would work but it keeps pulling up results that are negative values and already been executed.

Any ideas?

    If you are doing this though PHP it might be slightly easier to generate your timestamps in the code and use them in your query. Either way your basic query should be where it's greater than or equal to the current time, and less than the current time + 15 mins

    $now = strtotime('now');
$future = strtotime('now +15 minutes');

$sql = "Select * FROM cron WHERE `date_stamp` >= '".$now."' AND `date_stamp` <= '".$future."'";

    So it will grab everything that is in the future from the time it executes, but not more than 15 minutes in the future.

    If you want to keep in straight sql, that can be done too.

    SELECT * FROM cron WHERE `date_stamp` >= UNIX_TIMESTAMP() AND `date_stamp` <= UNIX_TIMESTAMP() + 900

    The +900 represents adding the 15 minutes to the current time stamp.

      Hmm tried doing that on SQL with:

      Select * FROM cron WHERE time >= UNIX_TIMESTAMP() && time <= UNIX_TIMESTAMP()+900

      problem is it didn't work 🙁

      I tried:

      Select * FROM cron WHERE time >= UNIX_TIMESTAMP()

      which works...and did

      Select * FROM cron WHERE time <= UNIX_TIMESTAMP()+900

      which also works...but when I put them together it doesn't work 🙁

        the use of the mysql BETWEEN() function would make it a little cleaner also i would use a datetime, is there really any need for unix timestamp?

        WHERE date_stamp BETWEEN(NOW(),NOW()+ INTERVAL 15 MINUTE)

          dagon;10969733 wrote:

          the use of the mysql BETWEEN() function would make it a little cleaner also i would use a datetime, is there really any need for unix timestamp?

          WHERE date_stamp BETWEEN(NOW(),NOW()+ INTERVAL 15 MINUTE)

          Select * FROM cron WHERE time BETWEEN(UNIX_TIMESTAMP(),UNIX_TIMESTAMP()+900)

          Didn't seem to work either 🙁

            Going to take your advice and use the datetime.

            Select * FROM cron WHERE time BETWEEN(NOW(),NOW()+ INTERVAL 15 MINUTE)

            comes back with an error:

            #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2

            SELECT *
            FROM cron
            WHERE stamp
            BETWEEN (
            NOW( ) , NOW( ) + INTERVAL 15
            MINUTE
            )
            LIMIT 0 , 30

              sorry i had the syntax for the 2 different betweens mixed up you want

              SELECT *
              FROM cron
              WHERE stamp
              BETWEEN
              NOW( ) AND NOW( ) + INTERVAL 15
              MINUTE

                dagon;10969739 wrote:

                sorry i had the syntax for the 2 different betweens mixed up you want

                SELECT *
                FROM cron
                WHERE stamp
                BETWEEN
                NOW( ) AND NOW( ) + INTERVAL 15
                MINUTE

                worked great thanks

                  Don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu above.

                    Write a Reply...