15,064
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→abnormal values of time data
| Line 162: | Line 162: | ||
* future time | * future time | ||
find normal values: Assume the data was generated in recent 10 years & not newer than today | find normal values: | ||
* MySQL: Assume the data was generated in recent 10 years & not newer than today | |||
** {{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` | ** {{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` | *** {{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` | ** {{code | code = SELECT * FROM `my_table` WHERE ( YEAR( CURDATE() ) - YEAR( `my_time_column`) <= 10 ) AND ( `my_time_column` < CURDATE() + 1); }} | ||
* | * MySQL: More precision to second compared with the above approach. Assume the data was generated in recent 10 years & not newer than current timestamp. | ||
** :{{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) | *** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the data (timezone issue) | ||