Page 1 of 1

question on dealing with the mysql timestamp feild

Posted: Tue Jan 20, 2004 6:04 pm
by m3rajk
im looking at the manual entry for timestamp and it's not clearing thisup for me. i have a table where i use it to find out when a user requested an email verification and want to delete those that are more then 24 hours old.

i set something up and currently have for a sql statement: DELETE FROM tabl WHERE timestampfeild<(NOW()-(24*60*60))

thing is i don't know how the timestamp feild works. can any of the db gurus tell me if that would remove all entries more than 24 hours old?
if not, where is the failure in my thought rocess of doing that and what do i need to know to correct it? and do you have any refrences aside fomr the mysql manual from mysql.com that i can use in the future?

Posted: Tue Jan 20, 2004 6:17 pm
by markl999
Try :
DELETE FROM tabl WHERE timestampfield < NOW() - INTERVAL 1 DAY

Posted: Wed Jan 21, 2004 3:05 am
by twigletmac
MySQL's timestamp is in the form YYYYMMDDHHMMSS - it isn't a UNIX timestamp:
http://www.mysql.com/doc/en/DATETIME.html

If you need to manipulate a date, you need MySQL's date and time functions (one type is that shown in markl999's SQL example):
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

Mac

Posted: Wed Jan 21, 2004 6:38 pm
by m3rajk
thanx. the second link actually helped a lot. i tried a test that worked to show his given statement worked but hadn't gotten a chence to look up interval and figureout why. checked here as sosn as i got back form workk today, saw the link and understood. =o)