CODEWITHSUNDEEP
phpmysqltimestampunix-timestamp
Nico
Nico

Reputation: 1081

MySQL convert every date field to timestamp

I developed a hybrid app with sencha touch 2 and mysql. The problem is that I made a big mistake. I saved all date fields in this format as varchar:

'23.05.2013 19:00'

And now its quite complicated to sort this field. I now would like to convert every field as timestamp.

Can I do this with a mysql query?

Or do I have to read every record with PHP, change the value and update the field?

Upvotes: 0

Views: 114

Answers (2)

Nico
Nico

Reputation: 1081

Thanks for the hints guys!

I managed it now to sort the table without converting it to timestamp:

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

Now I can sort the table or convert it to timestamp as N.B. suggested.

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10246

Suppose we want to convert '23.05.2013 19:01' to '2013-05-23 19:01:00', we can do with SUBSTRING_INDEX() and 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)

Finally, we need following SQLs;

// 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;

Upvotes: 1

Related Questions