14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
m
→abnormal values of time data
| Line 167: | Line 167: | ||
*** {{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 | *** {{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); }} | ||
* MySQL: | * MySQL: Assume the data was generated in recent 10 years & not newer than current timestamp. More precision to second compared with the above approach. | ||
** :{{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` <= 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) | ||