Mysql Regex, Sort out corrupted Time Formats

Question

I have a column called "Time" this column is currently set to Varchar since i have some corrupted Time in some updates.Some Time has non ASCII characters etc. So how can i sort out all these corrupted and non properly formated time fields and set to NULL ? So that i can safely convert the Time column back to DateTime. The normal time fields in the updates are usually in the format of 2013-07-24 14:37:56

I was thinking of sorting it out by doing something like :

SELECT * FROM updates WHERE TIME not LIKE '....-..-.. ..:..:..'

But i don't know if that is the right regex approach and most efficient.

enter image description here

If you just need to find those with non ASCII chars, [see this.](http://stackoverflow.com/questions/401771/how-can-i-find-non-ascii-characters-in-mysql) — Alon Adler, Apr 28 '14 at 10:27
@MYSQLnoob: Corrupted means what? Can you show some sample records? — Ravinder Reddy, Apr 28 '14 at 10:28
@NoobEditor because, it doesn't work ? No errors just not the expected results — , Apr 28 '14 at 10:32
@Alon_A i used that already. It's not enough for those which are corrupted and ASCII — , Apr 28 '14 at 10:34

Jerko W. Tisler · Answer 1 · 2014-04-28T11:27:10.743

0

SELECT * FROM updates WHERE DATE_FORMAT(`TIME`, '%Y-%m-%d %H:%i:%s') IS NULL

Function DATE_FORMAT will try to format TIME in 'Y-m-d H:i:s' format, if function is not able to format TIME it will return NULL, so that's how you'll know which one is not in appropriate format and then you can handle them manually or set them to null with UPDATE query

edited Apr 28 '14 at 11:27

answered Apr 28 '14 at 10:31

Jerko W. Tisler

996
9
29

1

When posting answers, please add and explanation describing what you changed or added or how it works or something. Even though your answer may be the solution, it's likely that some people who read this don't understand what it means or how it works. – Tim Apr 28 '14 at 11:21
i totally agree with you, sorry for not explained answer. i updated my answer with an explanation, hope it is ok now – Jerko W. Tisler Apr 28 '14 at 11:29

score 0 · Answer 2 · answered Apr 28 '14 at 10:34

0

You could try using MySQL STR_TO_DATE on your varchar column:

update updates set `time` = null 
where str_to_date(`time`,'%Y-%m-%d %H:%i:%s') is null;

answered Apr 28 '14 at 10:34

Tom Mac

9,693
3
25
35

score 0 · Answer 3 · answered Apr 28 '14 at 10:39

So how can i sort out all these corrupted and non properly formated time fields and set to NULL ?

If CAST of such TIME column returns a NULL, then you can set it to NULL or a desired date time string value.

Example with Select:

mysql> select @ts:=cast( '2013-07╞ƒ~¥14:37:56' as datetime ) ts, @ts is null;
+------+-------------+
| ts   | @ts is null |
+------+-------------+
| NULL |           1 |
+------+-------------+

Example for update:

update table_name 
   set time_coumn = null -- or a valid date time string value
 where cast( time_column as datetime ) is null

Once you have set the column value to a NULL, you can then set valid datetime values for those records which have NULL values.

Mysql Regex, Sort out corrupted Time Formats

3 Answers3