Jump to content

Archived

This topic is now archived and is closed to further replies.

Buyocat

MySQL + Date ()

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Dates are retrieved as strings eg '2006-03-05', so use

[code]$time = date ("F d, Y", strtotime($time));[/code]

Share this post


Link to post
Share on other sites
[!--quoteo(post=352308:date=Mar 6 2006, 05:22 PM:name=Buyocat)--][div class=\'quotetop\']QUOTE(Buyocat @ Mar 6 2006, 05:22 PM) [snapback]352308[/snapback][/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?

Share this post


Link to post
Share on other sites
[!--quoteo(post=352320:date=Mar 6 2006, 07:00 PM:name=fusionpixel)--][div class=\'quotetop\']QUOTE(fusionpixel @ Mar 6 2006, 07:00 PM) [snapback]352320[/snapback][/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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.