limitphp Posted November 7, 2008 Share Posted November 7, 2008 I have a table with a date field. I will be running queries on this table that will constantly be checking to see if the dates are newer than 24 hours, 7 days, 14 days, 21 days, 30 days, 60 days, 365 days. Will it be easier to set the date field in the table as an INT and use strtotime (unix timestamps) to do all the comparisons? or keep the date field as type date and do the comparisons a different way? Thanks. Quote Link to comment Share on other sites More sharing options...
n3ightjay Posted November 7, 2008 Share Posted November 7, 2008 you can just use strtotime('mysqlDateField') to get the same data Quote Link to comment Share on other sites More sharing options...
limitphp Posted November 7, 2008 Author Share Posted November 7, 2008 you can just use strtotime('mysqlDateField') to get the same data So, leave them as standard dates in the date field (2008-11-04) and just convert them in the query in the php code? How would you, for instance check a unix timestamp int if it was new than 24 hours or 7 days? Thanks. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 7, 2008 Share Posted November 7, 2008 Use a DATETIME type column . Then you can simply SELECT blah FROM tablename WHERE datecol > NOW() - INTERVAL 21 DAY to get recs posted in the last 21 days Quote Link to comment Share on other sites More sharing options...
F1Fan Posted November 7, 2008 Share Posted November 7, 2008 Any PHP date stamp (time(), strtotime(), mktime(), etc.) returns the number of seconds since Jan. 1st, 1970. So, simply create two PHP time stamps and compare them. The difference will be the number of seconds that separate them. Then, just apply logic to determine minutes, hours, days, or whatever between them. here's a great reference: http://www.w3schools.com/php/php_ref_date.asp Quote Link to comment Share on other sites More sharing options...
Barand Posted November 7, 2008 Share Posted November 7, 2008 MySQL has dozens of datetime functions to make date handling easier, almost all of which expect type DATE or DATETIME. If you store as anything else you have to convert to date/time to take advantage of these functions. eg SELECT DATEDIFF(CURDATE(), datecol) as ageInDays Quote Link to comment Share on other sites More sharing options...
limitphp Posted November 7, 2008 Author Share Posted November 7, 2008 Use a DATETIME type column . Then you can simply SELECT blah FROM tablename WHERE datecol > NOW() - INTERVAL 21 DAY to get recs posted in the last 21 days nevermind....I forgot to put AND after it. It works fine now. Wow that is easy! Thanks! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.