What is the best date format or timestamp format for storing a date in mysql and doing date comparison operations on it? Should I be using the date/datetime datatype or unix timestamps or php timestamps?
I want to do comparisons like selecting a date between today and two weeks ago.
yeah, The php date() function is what I've been using lately to. but I was wondering if it would be easier to do date calculations in mysql with a timestamp.
always use date() function... it is better as you won't be doing any calculations in SQL. It is always loading it into php(or something else via a DB frontend) and then computing on it... so use the date() or time() function. Most preferrably, use the time() function.
Also, the time() function is useful as you can easily compute the time difference between two time strings, by simple substraction, whereas the MySQL timestamp requires a lot of parsing and all.
I always use timestamps then convert to date as necessary in PHP. You just store them as 32bit integers IIRC.