mysql - 用于转换的 SQL 命令 12/20/2013 10 :33 AM to DATETIME fortmat

Question

我已经搜索了一个 SQL 命令来转换这种类型的格式，但我见过的最接近的是 STR_TO_DATE。读到这一点时我感到不知所措。

情况:

我有一个考勤程序，可以在员工扫描手指时下载姓名和时间戳。

该程序中没有选项可以将日期格式化为 MYSQL 使用的 DATETIME 格式，否则我现在就不会在这里。

相反，它存储一列“m/d/yyyy h/m AM/PM”。

我想也许尝试编写一个 php 脚本来转换并使用 Explode 来分隔日期和时间，但在尝试理解 STR_TO_DATE 时我再次陷入了迷茫。

如果我能指出正确的方向，我将不胜感激。

Answer 1

你将需要

 STR_TO_DATE('12/20/2013 10:33 PM', '%m/%d/%Y %h:%i %p')

这将选取特定的日期布局并将其放入 DATETIME 表达式中。

理解 STR_TO_DATE 的技巧是理解 DATE_FORMAT 的格式代码。你说得对，它很晦涩。

看这个。 http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

将此类时间数据存储在 DATETIME 数据类型的列中非常重要，因为 DATETIME 列可以建立索引并用于高效的范围扫描查找。大多数生产系统总是将此信息存储在 DATETIME(或有时是 TIMESTAMP)列中，并在检索时使用 DATE_FORMAT 或 php 等主机语言将其转换为所需的显示格式。

如果您有一个现有的表，我们将其称为“scan”，您可以向其中添加一个名为“scantimestamp”的 DATETIME 列，然后按如下方式填充它。

   UPDATE scan SET scantimestamp = STR_TO_DATE(`Timestamp`, '%m/%d/%Y %h:%i %p')

这将修复整个表格。然后您可以删除旧列。

首先在表格的副本上执行此操作，以确保不会弄乱它，您可以像这样复制表格:

  CREATE TABLE copyscan AS  SELECT * FROM scan

您也可以这样做:

  CREATE TABLE betterscan AS 
        SELECT Name,
               STR_TO_DATE(`Timestamp`, '%m/%d/%Y %h:%i %p') AS scantime
          FROM scan;

  RENAME TABLE scan TO oldscan;

  RENAME TABLE betterscan TO scan;

当您将新行插入改进的表中时，您可以使用这样的准备好的语句，并且您的时间戳将在插入时标准化。

  INSERT INTO scan  
        (`Name`, scantimestamp) 
 VALUES (?,      STR_TO_DATE(?, '%m/%d/%Y %h:%i %p')