MySQL date function vs. PHP date functions

[WendyLady]

Hey --

Another question for you. I'm building a database-driven site where I'm having to do a lot of date comparisons & subtracting dates to get number of days in between, and have so far been using the MySQL DATE functions. My dates are all stored as "2006-06-12" format.

However, it is driving me bonkers, and it is giving me unreliable results. Would it make it simpler for me if I simply used the PHP date functions & translated it back & forth? I'm already having to use formatting functions every time I display a date, so it wouldn't create extra work there. Will I get better results if my dates are stored as "1150154710"?

Another note -- I only need things to be as specific as number of days, not time or minutes or anything.

Thanks!

Wendy

[poirot]

Tell us what are "the unreliable results"... MySQL's date and time functions should be OK.

If, for some reason you want to convert these to UNIX timestamps you can use PHP's strtotime() or MySQL's UNIX_TIMESTAMP()

If the dates you are expecting to have are all after January 1 1970 UNIX timestamps may be easier to handle.

[a href=\"http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html[/a]
[a href=\"http://www.php.net/strtotime\" target=\"_blank\"]http://www.php.net/strtotime[/a]

[Caesar]

It would definitely make life easier. More flexible.

And for the record, I'd stay away from using MySQL's UNIX_TIMESTAMP()...as you may run into issues if your MySQL database is ever remote and the server times differ...things can get ugly. [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

[WendyLady]

After working on this for ages, I decided that, at least for the simple needs of the database I'm building, php's time() function will work best.

All I need to know is whether a date has passed (mark account as expired),

OR whether the numbers of days until that date is <60 (mark account as expiring soon), and show number of days until expiration.

I worked & worked & worked on the MySQL date & never could get it to work, but I had the time() working within a few minutes.

Also, I had lots of trouble getting it to round up a date all the time -- if the number of returned days is 0.2, I want it to still round up to 1 until it hits zero. That was making me a bit crazed trying to write a ridiculous rounding-up formula until I found --TA-DA! -- the ceil() function.

So now I'm off to re-edit my previously finalized code to use time() instead of MySQL DATE before I move on . . .

Thank you!
Wendy