Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→Time data: verify the data format
Line 291: | Line 291: | ||
=== Time data: verify the data format === | === Time data: verify the data format === | ||
Verify the value should be year-month-day format e.g. {{Template:Today}} | |||
<pre> | |||
SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) | |||
FROM `my_table` | |||
WHERE | |||
UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) IS NULL; | |||
</pre> | |||
Verify the value should be year/month/day format | Verify the value should be year/month/day format | ||
Line 301: | Line 310: | ||
</pre> | </pre> | ||
Verify the value should be hour:minute:second format | Verify the value should be hour:minute:second format e.g. {{CURRENTTIME}}:06 | ||
<pre> | <pre> | ||
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) | SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) | ||
Line 307: | Line 316: | ||
WHERE | WHERE | ||
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) IS NULL; | UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) IS NULL; | ||
</pre> | |||
Verify the value should be year-month-day hour:minute:second format e.g. {{Template:Today}} {{CURRENTTIME}}:06 | |||
<pre> | |||
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) | |||
FROM `my_table` | |||
WHERE | |||
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) IS NULL; | |||
</pre> | </pre> |