Can anybody shed any light on this, maybe I can use MySQL's time functions in my SELECT statement?

Yes, that's exactly what you should do. Something like:

SELECT col1, col2 FROM table 
    WHERE time > (TIMESTAMP() - (48 * 60 * 60))
[download]

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

Perl's time() function and MySQL's UNIX_TIMESTAMP() function seem to have completely different ideas about what time it is

Actually, they don't. The epoch from where time is calculated is January 1, 1970 for both MySQL and Perl, unless you happen to use a Mac OS, where the epoch is January 1, 1904.

$ perl -e 'print time(), "\n";' ; mysql -e "select unix_timestamp()"
1053097150
+------------------+
| unix_timestamp() |
+------------------+
|       1053097150 |
+------------------+
[download]

See also Perl's time and MySQL's timestamp for more about this issue.

 _  _ _  _  
(_|| | |(_|><
 _|
[download]

Hope that helps if you are getting more/less than you expect,
L~R

Why not?

SELECT * FROM YOURTABLE WHERE YOURCOL + 48 * 3600 > UNIX_TIMESTAMP();
[download]

That will work - but it will have to do a calculation for every value of YOURCOL. You may want to change it around to:


SELECT * FROM YOURTABLE WHERE YOURCOL < UNIX_TIMESTAMP() - 24 * 3600;
[download]


That way the db only does one calculation and can then compare it to the values of YOURCOL and even still use an index if there is one.

my @a=qw(random brilliant braindead); print $a[rand(@a)];