Jump to content

MySQL + Date ()


Buyocat

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.