[Resolved] Timing Help

[Sharif]

Hey,

I am creating a cron task and need some help. I need to make a where that deletes records based on a datetime field.

What it needs to do is, delete all teh queryies that are older than 12 hours with the name 'Guest', so this what I came up with but no luck, any suggestions?

DELETE FROM sessions WHERE name = 'Guest' AND UNIX_TIMESTAMP(time) >= " . time()-606012;

[Sxooter]

Are you using a database that undestands intervals? (late model mysql does, all versions of postgresql do, as does firebird)

DELETE FROM sessions WHERE name = 'Guest' AND time < now()-cast('12 hours' as interval)

where now() is replaced by whatever your database's buit in function for the current time is.

[Sharif]

How is this query? I was reading on interval stuff and got this:

DELETE FROM sessions WHERE time < NOW() - INTERVAL 12 HOUR

It doesn't use cast as yours do.

[Sxooter]

That should work too, but it's good form to at least enclose your interval in ' marks like this:

select now()-interval '12 hour';

Since many databases, like postgresql would choke on the unquoted 12 hour part.

The cast ('12 hours' as interval) is the SQL 92/98 spec way of doing it, and should (may? 😉 prove more transportable.

[Sharif]

Forgot to ask... trying to do this now:

Delete records that are have a date value that has passed. Meaning dates that are before today.

I do this right:

DELETE FROM table WHERE UNIX_TIMESTAMP(date) > now()