Date comparison query help needed

[drakal30]

I have date fields that are in unix time and I want to find all dates older than a specified date that the user entered in days, IE 35 for 35 days.  How would the query be structured?  I can think of a few ways to do this but they require some php code, I want to do it in one query SELECT COUNT statement.  Thanks in advance.

[BenInBlack]

date user enters = 2007-11-30 10:30:00

days user enters = 35 days = (35 days * 86400 second in a day)

 

SELECT UNIX_TIMESTAMP('2007-11-30 10:30:00'),(UNIX_TIMESTAMP('2007-11-30 10:30:00') - (86400 * 35));

 

this show the user data and the user date - 35 days

 

so SQL something like this

 

SELECT COUNT(*) FROM EVENTS WHERE EventDate <= UNIX_TIMESTAMP('2007-11-30 10:30:00') and EventDate >= (UNIX_TIMESTAMP('2007-11-30 10:30:00') - (86400 * 35))

[fenway]

date user enters = 2007-11-30 10:30:00

days user enters = 35 days = (35 days * 86400 second in a day)

 

SELECT UNIX_TIMESTAMP('2007-11-30 10:30:00'),(UNIX_TIMESTAMP('2007-11-30 10:30:00') - (86400 * 35));

 

this show the user data and the user date - 35 days

 

so SQL something like this

 

SELECT COUNT(*) FROM EVENTS WHERE EventDate <= UNIX_TIMESTAMP('2007-11-30 10:30:00') and EventDate >= (UNIX_TIMESTAMP('2007-11-30 10:30:00') - (86400 * 35))

NO!  Do not do it that way.  In fact, you should NEVER be storing unix timestamps, that's evil.  Change them to DATETIME fields if you can ASAP.

 

However, since you already this problem, you should convert it to a date, then use proper date math:

 

select count(*) from events where from_unixtime(event_date) > CURDATE() - INTERVAL 35 DAY

 

When you switch to the right field type, you can drop the conversion function, and then benefit from an index on this column.  I'll be more than happy to explain.

[drakal30]

Fenway please explain correct field type.  I use unix time stamps because I find them easier to do comparisons on.  Thanks.

[fenway]

Fenway please explain correct field type.  I use unix time stamps because I find them easier to do comparisons on.  Thanks.

Easier to do comparisons where? Not in mysql!  You get no benefit from munging a date into an integer like that -- keep it pristine and untouched as a DATETIME.  If you must convert it to a timstamp (like for php), you can always convert it the other way.

[BenInBlack]

Interval, I'm going to remember that, thx