Converting dates with PHP for DATETIME in SQL

问题:

I have a forum in PHP which takes a date like in the form dd/mm/yyyy hh:mm:ss. However, I need to insert it for SQL as a DATETIME in the format as yyyy-mm-dd hh:mm:ss. How can I convert this data?

回答1:

Your date time format is wrong: dd/mm/yyyy hh:mm:ss. Probably you mean d/m/Y H:i:s

If you have 5.3+ version there is safe way to convert the date time into another format. Here's an example:

$timestamp = '31/05/2001 12:22:56';
$timestamp = DateTime::createFromFormat('d/m/Y H:i:s', $timestamp);
echo $timestamp->format('Y-m-d H:i:s');

or if you like more procedural way:

$timestamp = '31/05/2001 12:22:56';
$timestamp = date_create_from_format('d/m/Y H:i:s', $timestamp);
echo date_format($timestamp, 'Y-m-d H:i:s');

Be careful with previous suggestions. Some are completely wrong and others could lead to errors.

回答2:

You can use the strtotime and date to rework the format.

$new_date = date( "Y-m-d H:i:s", strtotime( $old_date ) );

What this does is take your old date (dd/mm/yyyy hh:mm:ss), converts it to a unix timestamp that can then be used with the php date function to format the date to the desired format.

回答3:

Two of several possible ways:

1. Convert in code and then pass the converted value to mysql: $mysqldate = date( 'Y-m-d H:i:s', $phpdate );
2. Let mysql do the work by using its built-in functions: $query = "UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) ...";

回答4:

if you have datetime avaialable from a from like above format then u just need to use following function.

function localToMysql($dateTime){
  $date_chunks = explode('/', $dateTime);
    $time_chunks = explode(' ', $date_chunks[2]);
    $final_format = $time_chunks[0] . "-" . $date_chunks[1] . "-" . $date_chunks[0] . " " . $time_chunks[1];

return $final_format; }