Business Programming question

[freecartoonporn]

How to handle auto delete posts in mysql ?

i am working on webapp where users upload pics and choose expire date in future e.g. 10 minutes, 1 hour, 1 day, 1 month, 6 months, 1 year from the date of post creation.

How can i delete only those posts which are set for auto delete and whose date is expired ?

i can think of many ways., but cant decide which one to choose .


obviously cron job every minute will delete the expired posts.,

so should i save post_creation_date and post_expiration_date in datetime format or in unix timestamp format.

Thanks for your time.

i need logic, i can code mysqlf.


anybody ?

[redwhiteandblue]

I would have an expiration time in unix timestamp format, and when pulling out rows to display them or whatever else, have a condition on the query that only pulls rows that are before their expiration time. So rows that have gone past the expiration time appear to have been deleted even though they're still in the database. Then you can periodically delete them properly.

[magneto664]

keep your server time & unix timestamp format to avoid people from time zone +12 or -12

[freecartoonporn]

Originally Posted by redwhiteandblue

I would have an expiration time in unix timestamp format, and when pulling out rows to display them or whatever else, have a condition on the query that only pulls rows that are before their expiration time. So rows that have gone past the expiration time appear to have been deleted even though they're still in the database. Then you can periodically delete them properly.

thank you.

[freecartoonporn]

Originally Posted by magneto664

keep your server time & unix timestamp format to avoid people from time zone +12 or -12

i read this more times than i should and i still dont understand what are you trying to say.,

but i get your point.

but that doesnt matter in my case,

as while inserting records current time + expire after time = expiration time.

so there is no timezone issue., i guess., but i may be wrong.

[freecartoonporn]

i can think it like this way.

post creation time = X
post expiration timeframe (10 minutes, 1 hour, 1 day etc) = Y
post expiration time = Z

X + Y = Z

and my query would become,

if Z > current time {
remove records.
}

but Z gets changed everytime user edits post and changes expiration timeframe.

thanks

[Klen]

Originally Posted by freecartoonporn

i can think it like this way.

post creation time = X
post expiration timeframe (10 minutes, 1 hour, 1 day etc) = Y
post expiration time = Z

X + Y = Z

and my query would become,

if Z > current time {
remove records.
}

but Z gets changed everytime user edits post and changes expiration timeframe.

thanks

Maybe two columns called "original time" and "updated time" ?