Portal Home > Knowledgebase > Articles Database > Need help with mysql statement conerning datetime
Need help with mysql statement conerning datetime
| Posted by ilyash, 12-04-2007, 07:52 PM |
| So I have a table where each row has 2 fields.. start_datetime and end_datetime... both datetimes.
What I need to do is have the row be deactivated [deleted from the table is fine] after end_datetime is up.
delete from table where (unix_timestamp(table.end_datetime)-unix_timestamp(now()))<0
problem is.. isnt this query very inefficient? for a large table.. wouldnt this take way too long?
Also when should this deletion check thing occur? when i write a new row into db? or when i read.. im thinking read since reading will take place much more often than writing.. but in that case this intensive query will take place too many times.
Anybody have a better solution?
btw, each row also has a field called "status" which can be deactivated instead of deleting it. (dont know if that helps)
thanks.
|
| Posted by foobic, 12-04-2007, 08:19 PM |
| You could just use "WHERE table.end_datetime < NOW()". With an index on the column it should be as efficient as it gets.
Looks like it's just a cleanup task anyway so I'd run it in a daily cron job at a time when the site is generally quiet.
|
| Posted by ilyash, 12-04-2007, 08:24 PM |
| yeah that could work too^
but if I run it as a daily cron.. I run the risk of having old ones showup before the cron runs.. and since most of the end_datetimes are only 4-5 hrs later than start_datetime
Maybe I should have another table.. where I store the last time the job runs...
then first check if the difference btwn now and then is < than say an hr...
if so.. run the job
?
|
| Posted by foobic, 12-04-2007, 08:52 PM |
| Or just run it every hour. Why do you think it's so inefficient? Have you tried the simplified query?
|
| Posted by ilyash, 12-04-2007, 09:56 PM |
| you sure its ok to compare datetimes using just the > operator?
|
| Posted by foobic, 12-04-2007, 11:33 PM |
| Here's an example from an impeccable source:
Note that "DATE_SUB(CURDATE(),INTERVAL 30 DAY)" only needs to be calculated once. They could have used "DATE_ADD(date_col,INTERVAL 30 DAY)" but it would be much less efficient.
|
Add to Favourites 
Print this Article
Also Read
Private DNS (Views: 805)