MySQL将每个日期字段转换为时间戳


MySQL convert every date field to timestamp

我开发了一个sencha touch 2和mysql的混合应用程序。问题是我犯了一个大错误。我将这个格式的所有日期字段保存为varchar:

'23.05.2013 19:00'

现在对这个字段进行排序是相当复杂的。现在我想将每个字段转换为时间戳。

我可以用mysql查询吗?

或者我必须用PHP读取每条记录,更改值并更新字段吗?

假设我们要将'23.05.2013 19:01'转换为'2013-05-23 19:01:00',我们可以使用SUBSTRING_INDEX()MAKEDATE()

SET @dt := '23.05.2013 19:01';
SELECT @dt, SUBSTRING_INDEX(@dt, '.', 1) AS day,
  SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, '.', 2), '.', -1) AS month,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, '.', 3), '.', -1), ' ', 1) AS year,
  SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, ' ', -1), ':', 1) AS hour,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, ' ', -1), ':', 2), ':', -1) AS minute,
  '00' AS second;
+------------------+------+-------+------+------+--------+--------+
| @dt              | day  | month | year | hour | minute | second |
+------------------+------+-------+------+------+--------+--------+
| 23.05.2013 19:01 | 23   | 05    | 2013 | 19   | 01     | 00     |
+------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)
SELECT @dt, CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, '.', 3), '.', -1), ' ', 1), '-',
  SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, '.', 2), '.', -1), '-',
  SUBSTRING_INDEX(@dt, '.', 1), ' ',
  SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, ' ', -1), ':', 1), ':',
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, ' ', -1), ':', 2), ':', -1), ':00') AS datetime;
+------------------+---------------------+
| @dt              | datetime            |
+------------------+---------------------+
| 23.05.2013 19:01 | 2013-05-23 19:01:00 |
+------------------+---------------------+
1 row in set (0.00 sec)

最后,我们需要以下sql;

// add temp column
ALTER TABLE tab ADD valid_datetime DATETIME;
// update temp column to valid datetime value
UPDATE tab SET valid_datetime = CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(wrong_datetime, '.', 3), '.', -1), ' ', 1), '-',
  SUBSTRING_INDEX(SUBSTRING_INDEX(wrong_datetime, '.', 2), '.', -1), '-',
  SUBSTRING_INDEX(wrong_datetime, '.', 1), ' ',
  SUBSTRING_INDEX(SUBSTRING_INDEX(wrong_datetime, ' ', -1), ':', 1), ':',
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(wrong_datetime, ' ', -1), ':', 2), ':', -1), ':00');
// rename temp column to old column
ALTER TABLE tab DROP wrong_datetime, CHANGE valid_datetime wrong_datetime DATETIME;

谢谢大家的提示!

我现在管理它排序表而不将其转换为时间戳:

SELECT datefield FROM mytable ORDER BY STR_TO_DATE(datefield, '%d.%m.%Y %H:%i') DESC

现在我可以排序表或将其转换为时间戳N.B.建议。