Jump to content


Photo

MySQL date function vs. PHP date functions


  • Please log in to reply
3 replies to this topic

#1 WendyLady

WendyLady
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 12 June 2006 - 11:26 PM

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

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 13 June 2006 - 12:42 AM

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]
~ D Kuang

#3 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 13 June 2006 - 12:46 AM

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\" /]
PHP Ninja

#4 WendyLady

WendyLady
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 13 June 2006 - 09:04 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users