convert date string to mysql datetime field
PhpMysqlPhp Problem Overview
I have a bunch of records with dates formatted as a string such as '04/17/2009'
I want to convert them to a mysql datetime field
I plan to use a foreach loop to read the old date value and insert the newly formatted value into a new field in each record
what would be the best way to convert that string...I thought php might have a way to do it automatically?
thanks
Php Solutions
Solution 1 - Php
First, convert the string into a timestamp:
$timestamp = strtotime($string);
Then do a
date("Y-m-d H:i:s", $timestamp);
Solution 2 - Php
If these strings are currently in the db, you can skip php by using mysql's STR_TO_DATE() function.
I assume the strings use a format like month/day/year where month and day are always 2 digits, and year is 4 digits.
UPDATE some_table
SET new_column = STR_TO_DATE(old_column, '%m/%d/%Y')
You can support other date formats by using other format specifiers.
Solution 3 - Php
Use DateTime::createFromFormat like this :
$date = DateTime::createFromFormat('m/d/Y H:i:s', $input_string.' 00:00:00');
$mysql_date_string = $date->format('Y-m-d H:i:s');
You can adapt this to any input format, whereas strtotime() will assume you're using the US date format if you use /, even if you're not.
The added 00:00:00 is because createFromFormat will use the current date to fill missing data, ie : it will take the current hour:min:sec and not 00:00:00 if you don't precise it.
Solution 4 - Php
$time = strtotime($oldtime);
Then use date() to put it into the correct format.
Solution 5 - Php
I assume we are talking about doing this in Bash?
I like to use sed to load the date values into an array so I can break down each field and do whatever I want with it. The following example assumes and input format of mm/dd/yyyy...
DATE=$2
DATE_ARRAY=(`echo $DATE | sed -e 's/\// /g'`)
MONTH=(`echo ${DATE_ARRAY[0]}`)
DAY=(`echo ${DATE_ARRAY[1]}`)
YEAR=(`echo ${DATE_ARRAY[2]}`)
LOAD_DATE=$YEAR$MONTH$DAY
you also may want to read up on the date command in linux. It can be very useful: http://unixhelp.ed.ac.uk/CGI/man-cgi?date
Hope that helps... :)
-Ryan
Solution 6 - Php
SELECT *
FROM table_name
WHERE CONCAT( SUBSTRING(json_date, 11, 4 ) , '-', SUBSTRING( json_date, 7, 2 ) , '-', SUBSTRING( json_date, 3, 2 ) ) >= NOW();