4 ms·
Ask HN: What's your strategy for inconsistent date formats?
Working on a data cleaning tool and running into a problem I haven't found a clean solution for: user-uploaded CSVs where a single date column has multiple formats. Not just ISO vs US vs European - but also relative formats ("Jan-23"), written formats ("15th January 2023"), and partial dates all mixed together.
The naive approach of trying a list of strptime format strings in order breaks on ambiguous dates like "01/02/03" - is that January 2nd 2003, or February 1st 2003, or 1st February 2003? The answer depends on the locale and context that we often don't have.
Our current approach: scan the first 50 non-null values, rank format candidates by match frequency, flag ambiguous dates for user confirmation, and store the detected format alongside the column metadata for future imports. We handle about 94% of real-world mixed-format columns automatically, but the remaining 6% need user input.
Some patterns that came up more often than expected in real datasets: dates with ordinal suffixes ("1st", "2nd", "3rd"), fiscal quarter notation ("Q3 2024"), and Unix timestamps stored as strings.
Curious what approaches others have used, particularly around the ambiguity resolution step.
- stop50 7mo agoLocalization for ui only. Im- and exported data only in standard formats
- Gyanangshu 7mo agoThat's the ideal, but unfortunately not always an option when you're on the receiving end. We're building a data cleaning tool, so the whole point is dealing with messy user-uploaded CSVs where we don't control the export format. If we could mandate ISO 8601 everywhere, life would be much simpler. But the reality is people copy-paste from Excel, export from legacy systems, or hand-edit CSVs, and we need to handle what shows up.
- freakynit 7mo agoA known issue.. have faced this personally long back (had to let it go back then since the use-cases was no more valid) ... but, will this help? https://github.com/freakynit/smart-date-parser https://github.com/freakynit/smart-date-parser This does maintain context based on past successful parses. Disclaimer: This is fully opus generated, but do have test cases (in usage.js ... i know.. it's not what it's for.. but it is what it is).
- Gyanangshu 7mo agoThanks for sharing this! The context-based approach is interesting. Maintaining state from past successful parses to resolve ambiguity is essentially what we do too, though we batch it (scan first 50 values) rather than doing it incrementally. The incremental approach has the advantage of adapting mid-column if formats shift, which we've seen in datasets that were manually concatenated from different sources. Will take a look at the repo.
- freakynit 7mo agoAll thanks to Opus :)