0

I am sure this must be a common question but I cannot find it anywhere so here it goes:

I have dates in this format: 09/Jul/2003:00:03:48 -0300 and I want to push them into a DATETIME field in MySQL.

What is the easiest way to do this? Pushing them in as is results in them being zeroed.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
sixtyfootersdude
  • 25,859
  • 43
  • 145
  • 213

3 Answers3

5

MySQL doesn't include the timezone in DATETIMEs, but you can use:

STR_TO_DATE('09/Jul/2003:00:03:48', '%d/%b/%Y:%H:%i:%s')

...to convert a string representation of a date/time into a DATETIME for storing in the database.

Reference:

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Awesome thanks! I knew there must be some easy way to do that. I was looking through php functions and just starting to pull out regex. This is *way* easier. – sixtyfootersdude Nov 12 '10 at 21:39
  • @sixtyfootersdude: If you're looking for the PHP "equivalent" (it doesn't convert, but it parses it), try [strptime](http://php.net/strptime). – netcoder Nov 12 '10 at 21:45
0

As far as I know can not be changed and has to be in the format below:

'YYYY-MM-DD HH:MM:SS'

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Drewdin
  • 1,732
  • 5
  • 23
  • 35
0

My favorite function in all of PHP is strtotime(). If that can't interpret the input then I probably can't code something that could.

So I would do something like:

mysql_real_escape_string(date('Y-m-d H:i:s', strtotime($stragely_formatted_date)));

to prepare a DATE string to give to MySQL.

As always, think about timezones in your the design.

The mysql_real_escape_string() is probably unnecessary but I always feel more secure when I use it.