[SOLVED] PHP checking dates

[limitphp]

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.

[n3ightjay]

you can just use strtotime('mysqlDateField')

 

to get the same data

[limitphp]

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.

[Barand]

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

[F1Fan]

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

[Barand]

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

[limitphp]

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!