php date to mysql returns 0's [duplicate]

Question

Hello i am trying to add a date time from PHP into my MySQL database, in the database I have tried date time and time stamp and I have tried it with and without current_time set as the default option, in my php I have the following.

date_default_timezone_set("Europe/London");
$date = date('m/d/Y h:i:s a');

My hope was to just add the $date into the value's part of the upload query however in all cases it comes back as 0's, it echo's the date fine, however it uplaod's 0's, any help is appreciated, Thanks.

Your date format is not the correct format for MySQL - You need to save it as Y-m-d H:i:s — Brendan Bullen
– Brendan Bullen, Commented Nov 14, 2013 at 14:40

John Conde · Accepted Answer · 2013-11-14 14:41:31Z

5

'm/d/Y h:i:s a is not a standard MySQL datetime format so unless you are storing it as varchar/char you will get the results you are seeing.

Your options are to:

Store the date in a standard format (datetime Y-m-d H:i:s, timestamp) and convert it to whatever format you want during the query (recommended)
Store it as a string but lose all of the datetime functionality MySQL offers. Losing this functionality will make working with dates in your queries and PHP very painful and is not recommended to do.

answered Nov 14, 2013 at 14:41

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mr. Alien Over a year ago

Another option is to use strtotime() and than convert the date via php

jphillip724 Over a year ago

Thanks i got it working, dont know where the A came from :S, once i swapped the m/d/y to Y/m/d it worked, Thanks for help will mark as answer when i can :)

Brendan Bullen Over a year ago

@jphillip724 This is VERY IMPORTANT - Make sure you have an uppercase H as well - lower case will result in 12 hour time and half of your times could be 12 hours out. There is a difference between H:i:s and h:i:s!

user6743474 Over a year ago

I know this is an old thread, and I can understand the comment about maintaining dates in mySQL but I can vouch that trying to get the formats into mySQL can be sometimes be very problematic. I have found that manipulating outside mySQL in say PHP can give you a bit more flexibility as you know that you are always dealing with text when going back to mySQL. Anyway my 2c

Collectives™ on Stack Overflow

php date to mysql returns 0's [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related