14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→abnormal values of time data
| Line 164: | Line 164: | ||
find normal values: Assume the data was generated in recent 10 years & not newer than today | find normal values: Assume the data was generated in recent 10 years & not newer than today | ||
* MySQL: | * MySQL: | ||
** {{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` > CURDATE() + 1);}} | ** {{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` > CURDATE() + 1);}} | ||
*** {{exclaim}} NOT {{code | code = `my_time_column` > CURDATE() }}。 ex: {{code | code = CURDATE() }} is {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}}. Which is the same with {{CURRENTYEAR}}-{{CURRENTMONTH}}-{{CURRENTDAY2}} 00:00:00 | |||
** {{code | code = SELECT * FROM `my_table` WHERE ( YEAR( CURDATE() ) - YEAR( `my_time_column`) <= 10 ) AND ( `my_time_column` > CURDATE() + 1); }} | ** {{code | code = SELECT * FROM `my_table` WHERE ( YEAR( CURDATE() ) - YEAR( `my_time_column`) <= 10 ) AND ( `my_time_column` > CURDATE() + 1); }} | ||
** More precision to second: {{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` > CURRENT_TIMESTAMP);}} | |||
*** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the data (timezone issue) | |||
== duplicate data == | == duplicate data == | ||