On the January 1st this year I got one very odd email from online store I purchased something from couple of years ago (BTW, it was the worst online shopping experience ever: it worked like a soviet hospital, not an internet shop).
Anyway, it was a ‘Happy Birthday‘ email, and no, my birthday is not the January, 1st.
It’s very unlikely that I entered 01/01 as my DOB: I try not to tell it to online stores when the field is not required; even when it is I prefer to enter 02/29 to minimize the number of such automated HB congratulations.
So it got me thinking: why on earth would their database contain 01/01 as my DOB?
My best guess is that they are storing date of birth as a unix timestamp. And at some point the value I provided (NULL) got converted to 0 (zero).
And zero seconds since the Epoch means 01/01/1970: so, their software decided I just turned 45 and sent me a happy birthday email (I didn’t think it was possible, but my opinion about that particular store got worse).
What lessons should developers learn from this story?
- Never use TIMESTAMP when you can use DATE data type. They are different, and date of birth is definitely a DATE.
- Do not confuse NULL and zero (or empty string). NULL is a special value, meaning ‘NOTHING’. There are cases, when it’s the same as zero (e.g.: number of items purchased), but generally it’s not the same (as in my example with non-entered DOB).
- Use DEFAULT values properly. 0 and NULL are not necessary the same.
- Treat data carefully during upgrades/conversions. I’m not sure if the DOB field was there when I was registering at the website, but it could be introduced in the database later – and all old records may have got their values for DOB field set to 0 instead of NULL.
In my last guess is true, I’m not the only one who got that stupid ‘Happy Birthday’ notification when we should have received ‘Happy New Year’ notification instead.
Happy New Year to all (if any) readers of this blog!