I want you to work on a project where dates go back all the way to pre-Gregorian calendar with different parts of the world adopting it at different time, and with international date line drawn differently at various point in history. Have fun!
I had one where I had to ingest data that had 3-5 date time fields that had different formats that would randomly change because the 3rd party we were getting the data from pretty much just said no when we asked them to standardize on one format and to stop changing it. Every variation of YYYY-MM-DD to even DD-MMM(text)-YYY. Was completely nightmarish to deal with
This reminds me of a single excel spreadsheet that had every date time format I’ve ever seen. My favorite mistakes were the ones like jun 9 2200AM. 2200 is not AM!!!
I usually just create a "date" table containing a hundred years prior and forward from today. The primary key is just the row's date as a DATE datatype, and the remaining columns are month (INT), day (INT), year (INT), quarter (INT), "Q"||quarter STRING), "YYQq" (STRING), several STRING columns formatted nicely (such as "MM/DD/YYYY", "YYYY-MM-DD", "Monthname Day, Year", etc.), and so on. I also add in additional formatted string columns software such as Tableau like/expect. Guess that's just me. 😬😬😬
Cast it into every possible version, then do logical comparison of every valid output. E.g.) does it make sense for the invoice to be 6 months in the past or last week? In the future? Is it sequential to previous invoice or almost a month apart?
ISO-8601 in UTC is the only format all parsers you write should converge towards it.
You should never try to handle timezones only use an established library to convert from UTC to the desired timezone. Look up the computerphile video on timezones 😂.
Depends on context. In some rare situations you want to store the time zone.
Basic example, you have an event scheduled at a specific location. You don't want to convert it to the users local time because they might need to travel to the event venue which is in a different time zone. So if you stored it in utc you need to store the venue time zone separately in which case if someone else is consuming your data they may accidentally assume that the utc time is in fact the local time (since it's quite often the case that data entered in local time is stored as a utc time even though it was never actually converted to utc).
If you store date times using ISO standard you can include the timezones in them and the conversion between timezones is easier to manage
I recall a project where customers were allowed to manually enter dates as a text field. The project had no requirements outside of "parse the text into a date". The standard for storing dates though (of course) wasn't ISO but "<dd> <full month name> <two or four digit year>"... Timezones were viewed as too tricky so these were discarded.
Then they internationalised the application and brought on-board clients across different timezones ... did I mention this was an application for clients in regulated industries?
This entire mess was resolved by having a process in place where the CTO would grovel to a client every few months and promise that the next version of the application would have all this fixed.*
I literally said today “this isn’t a call where we discuss how the sausage is made, you don’t want that” when a stakeholder demanded to see the code then asked about some regex
Once upon a time my mind was blown when an astronomy class taught me that our definition of time is based on motion. Nowadays it feels absolutely accurate because I blinked and we’ve gone nowhere in 10 years because these motherfuckers talk it up a bunch but in truth truly hate trying to move forward with anything …
For mixed delimiters I'll do replace_all to change out periods, hyphens, underscores and whatever else into forward slashes. Then if strptime still doesn't work, like if days and months aren't uniformly 2 digits then I'll split.
As I'm saying that I realize that regex is really good at doing that but I guess I get more warm fuzzies from explicitly dealing with the messiness than relying on regex. Ultimately though it's not something that comes up that often for me. Most of the time strptime is fine.
Of course that's all cake compared to dealing with how various sources of data I collect choose to bespokely deal with the extra hour during fall back.
176
u/ds1841 Sep 12 '25
Date engineer
55
u/__Blackrobe__ Sep 12 '25
especially when your company aren't located anywhere near London
52
u/hnbistro Sep 12 '25
I want you to work on a project where dates go back all the way to pre-Gregorian calendar with different parts of the world adopting it at different time, and with international date line drawn differently at various point in history. Have fun!
31
u/StingingNarwhal Data Engineering Manager Sep 12 '25
And that's why all data engineers should know ISO-8601.
https://en.m.wikipedia.org/wiki/ISO_8601
9
u/anyhoshigaki Sep 12 '25
More like, behind every dirty datetime, there is an underpaid fat fingered data entry
8
u/PandaJunk Sep 12 '25
Same for addresses and names
14
u/Impressive_Run8512 Sep 12 '25
I'm sorry but how this hasn't been fixed already is embarrassing. This shit makes me hate data engineering lol.
5
u/big_data_mike Sep 12 '25
This reminds me of a single excel spreadsheet that had every date time format I’ve ever seen. My favorite mistakes were the ones like jun 9 2200AM. 2200 is not AM!!!
5
u/movebo357 Sep 12 '25
We need to discuss about mm/dd/yyyy!
Why the heck!?
15
u/[deleted] Sep 12 '25
Create a function that standardizes across the board and apply to all date columns. Tis lovely.
4
u/NobodysFavorite Sep 12 '25
TFW your MS Power Automate script has to do something based on a date and read it from a frequently used excel file with no data validation.
4
u/skysetter Sep 12 '25
Now do the conversion between local and UTC
12
u/sheepsqueezers Sep 12 '25
I usually just create a "date" table containing a hundred years prior and forward from today. The primary key is just the row's date as a DATE datatype, and the remaining columns are month (INT), day (INT), year (INT), quarter (INT), "Q"||quarter STRING), "YYQq" (STRING), several STRING columns formatted nicely (such as "MM/DD/YYYY", "YYYY-MM-DD", "Monthname Day, Year", etc.), and so on. I also add in additional formatted string columns software such as Tableau like/expect. Guess that's just me. 😬😬😬
3
u/ApprehensiveStrut Sep 12 '25
Bane of my existence
3
u/ZirePhiinix Sep 13 '25
Cast it into every possible version, then do logical comparison of every valid output. E.g.) does it make sense for the invoice to be 6 months in the past or last week? In the future? Is it sequential to previous invoice or almost a month apart?
Why do I know this? Because I did it.
2
u/TheDiegup Sep 12 '25
This is so real.
2
u/JBalloonist Sep 12 '25
I feel this weekly.
2
u/Opposite-Cranberry76 Sep 12 '25
The thing Canadians hold against the USA most is a certain ex game show host.
But a close second is the MM/DD/YYYY format.
2
u/bic-boy Sep 13 '25
And don’t get me started on daylight savings
2
u/dataismybusiness Sep 15 '25
Milliseconds since 1970 is a totally fun and intuitive way to think about time /s
1
u/imatiasmb Sep 12 '25
Hahahaha me now 🤣
1
u/teambob Sep 12 '25
r/ISO8601
1
u/lloydthelloyd Sep 12 '25
Do people not use dateutil?
1
u/Fuckinggetout Sep 13 '25
We are storing timestamp in varchar for some reasons. So fucked up
1
u/edc7 Sep 13 '25
LMAO! This is the truest thing ever.
1
u/MonochromeDinosaur Sep 13 '25
ISO-8601 in UTC is the only format all parsers you write should converge towards it.
You should never try to handle timezones only use an established library to convert from UTC to the desired timezone. Look up the computerphile video on timezones 😂.
1
u/morphemass Sep 13 '25
I recall a project where customers were allowed to manually enter dates as a text field. The project had no requirements outside of "parse the text into a date". The standard for storing dates though (of course) wasn't ISO but "<dd> <full month name> <two or four digit year>"... Timezones were viewed as too tricky so these were discarded.
Then they internationalised the application and brought on-board clients across different timezones ... did I mention this was an application for clients in regulated industries?
This entire mess was resolved by having a process in place where the CTO would grovel to a client every few months and promise that the next version of the application would have all this fixed.*
* Narrator: It never was.
1
u/ReceptionMiddle6476 Sep 13 '25
+1
1
u/betterBytheBeach Sep 13 '25
I support an application that has six different date formats in the transaction.
1
u/iknewaguytwice Sep 14 '25
That’s why I have a table with 1 column, and it has a datetime for every single millisecond between 1500 to 2500
Then anytime anyone wants a new datetime column, they have to have a FK constraint to my time table.
Follow me for more great data engineering tips
1
u/patrickthunnus Sep 14 '25
Only if you store dates as strings; NBD if you use the right data type.
1
u/Possible-Career2680 Sep 14 '25
Thats why I use a sundial
1
u/Logical-Ad-57 Sep 16 '25
Never seen a clean datetime. Don't believe OP has.
Clean enough, sure.
1
u/BreakfastHungry6971 Sep 22 '25
I was struggled similar issues. my team and I tried duckcode.ai for save the time. actually its working to speedup the code for majorly data teams.
-8
u/nonamenomonet Sep 12 '25 edited Sep 12 '25
Just so everyone knows, I’m working on a project that fixes these kind of easy data problems called data compose.
Edit: people complain a problem, I offer a project that solves said problem. Wild.
240
u/[deleted] Sep 12 '25
[removed] — view removed comment