Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→Time data: the value should be YYYY/MM/DD format
(→Verify the format of field value: + Time data: the value should be YYYY/MM/DD format) |
|||
| Line 290: | Line 290: | ||
</pre> | </pre> | ||
=== Time data: the | === Time data: verify the data format === | ||
Verify the value should be year/month/day format | |||
<pre> | <pre> | ||
SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) | SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) | ||
| Line 297: | Line 298: | ||
WHERE | WHERE | ||
UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) IS NULL; | UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) IS NULL; | ||
</pre> | |||
Verify the value should be hour:minute:second format | |||
<pre> | |||
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) | |||
FROM `my_table` | |||
WHERE | |||
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) IS NULL; | |||
</pre> | </pre> | ||