Data cleaning: Difference between revisions

Jump to navigation Jump to search
602 bytes added ,  15 June 2020
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>
Anonymous user

Navigation menu