question on dealing with the mysql timestamp feild

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

question on dealing with the mysql timestamp feild

Post 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?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Try :
DELETE FROM tabl WHERE timestampfield < NOW() - INTERVAL 1 DAY
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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)
Post Reply