Jump to content


Photo

MySQL + Date ()


  • Please log in to reply
5 replies to this topic

#1 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 06 March 2006 - 11:02 PM

I'm having a problem with MySQL or maybe it's PHP but at any rate what happens is that dates stored in the MySQL are coming out as Dec. 31 1969 when formated by Date(). The code I have looks something like this:
insert query:
INSERT INTO table (time) VALUE (NOW())
I have tried to use both a Date-Time and Timestamp as the values to store the date, when I retreive it I format it
$time = date ("F d, Y", $time);
print $time // get a completely wrong output
however if I just print time without formating
print $time // gets 2006-03-06 ...
So, is there a conversion error somewhere that I can fix, and if so how? Thanks for your help, Buyo.
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 06 March 2006 - 11:17 PM

Dates are retrieved as strings eg '2006-03-05', so use

$time = date ("F d, Y", strtotime($time));

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 06 March 2006 - 11:22 PM

Thanks Barand, problem solved

Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#4 fusionpixel

fusionpixel
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts

Posted 07 March 2006 - 12:00 AM

[!--quoteo(post=352308:date=Mar 6 2006, 05:22 PM:name=Buyocat)--][div class=\'quotetop\']QUOTE(Buyocat @ Mar 6 2006, 05:22 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Thanks Barand, problem solved
[/quote]

Sorry, I hope Im not stealing your thread, but I read on a book that sometimes is better let mySQL handle items like this to save power in the front end or save power in php.

what do you guys think? Is that a normal procedure?
Did you know there is a manual for PHP? [a href="http://" target="_blank"]http://www.php.net[/a]
Did you know there is a manual for mySQL? [a href="http://" target="_blank"]http://www.mysql.org[/a]

#5 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 07 March 2006 - 12:04 AM

[!--quoteo(post=352320:date=Mar 6 2006, 07:00 PM:name=fusionpixel)--][div class=\'quotetop\']QUOTE(fusionpixel @ Mar 6 2006, 07:00 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Sorry, I hope Im not stealing your thread, but I read on a book that sometimes is better let mySQL handle items like this to save power in the front end or save power in php.

what do you guys think? Is that a normal procedure?
[/quote]

depends on the situation. i typically end up with using barand's solution simply because i may use that same date variable to echo any number of different ways using the date() function throughout the page. however, if you're only making one call to grab a very specific date format, you could always let SQL handle it with the DATE_FORMAT function. it really depends on how you're using it.

a more technical answer: yes, it is faster to let SQL handle the function call if you're only using it that once. however, as soon as you have to query for multiple formats of the date, it is easier and faster to query once for the datestamp and let PHP format it each time you need it displayed.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 07 March 2006 - 03:58 AM

I'm such a rebel. I store all my dates as BIGINT(14) - "20060306184623". You can still do date math on them in SQL because it's a recognized format, though sometimes you have to be smart about it or you lose your index.

Then I have my own custom formatting function because I dislike PHP's syntax.

As long as you're consistent throughout your application, whatever strategy you choose shouldn't be a problem, in terms of performance or anything else. Converting dates can be complex, but in terms of processing time it's pretty trivial.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users