在MySQL中将列的格式更改为时间戳

问题描述：

I have a CSV file that I am importing into MySQL using PHP but the problem is that the timestamp is in the following format

01/02/2014 00:00:00

I have read about MySQL timestamp and it only accepts in the following format

01-02-2014 00:00:00

from what I know. I am wondering if there is a way that I can convert the column which I have set as varchar into timestamp and convert it into a format that MySQL will accept ?

Is there an SQL query or a PHP function that I can use to do this? Also, the no of rows exceeds more than a million so I want it to be as fast as possible.

Any suggestions would be appreciated.

我有一个CSV文件，我使用PHP导入MySQL，但问题是时间戳在下面格式 p>

  01/02/2014 00：00：00 
  code>  pre> 
 
 我已阅读有关MySQL时间戳的信息 只接受以下格式 p> 
 
 
  01-02-2014 00:00:00 
  code>  pre> 
 
  知道。 我想知道是否有一种方法可以将我设置为varchar的列转换为时间戳并将其转换为MySQL将接受的格式？   p> 
 
 
我可以使用SQL查询或PHP函数来执行此操作吗？ 而且，行数超过一百万，所以我希望它尽可能快。   p> 
 
 
任何建议都将不胜感激。 p> 
  div>

答

If your MySQL column is DATE type:

$date = date('Y-m-d', strtotime(str_replace('-', '/', $date)));

If your MySQL column is DATETIME type:

$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date)));

You haven't got to work strototime(), because it will not work with dash - separators, it will try to do a subtraction.

Update, the way your date is formatted you can't use strtotime(), use this code instead:

$date = '02/07/2009 00:07:00';
$date = preg_replace('#(\d{2})/(\d{2})/(\d{4})\s(.*)#', '$3-$2-$1 $4', $date); 
echo $date;

Output:

2009-07-02 00:07:00

答

You will see in the following link (Convert one date format into another in PHP) that One can use either DateTime or strtotime() with date() to create the new format.

I would use the DateTime:

<?php
$date = date_create_from_format('j-M-Y', '15-Feb-2009');
echo date_format($date, 'Y-m-d');
?>

This solution is given by the php documentation.(http://php.net/manual/en/datetime.createfromformat.php)

在MySQL中将列的格式更改为时间戳

相关推荐