Store date of birth in database as UNIX time?

Question

I want to store the date of birth as a UNIX timestamp in my database, because this keeps the database small and it speed up the queries.

However, when converting the date of birth to a UNIX time using strtotime, it will output the wrong value, namely the inputted value with one hour difference. I know setting the date_default_timezone_set('UTC'); will output the correct date of birth in UNIX time, but the date of birth has nothing to do with where someone lives, right? Date of birth stays the date of birth, no matter where someone lives.

So in example

$bday = 20;
$bmonth = 6;
$bYear = 1993;

strtotime($cBday.'-'.$cBmonth.'-'.$cByear) // output: 740527200 == Sat, 19 Jun 1993 22:00:00 

PS: Database field is defined as: bDate int(4) UNSIGNED

Hathorn

"but the date of birth has nothing to do with where someone lives, right?" -Correct. Did you have another question? – Hedwighedwiga

Of course the date of birth is connected to where one was born. A person born on 1am on 01.02.2015 in Paris, would have been born on 31.01.2015 if his mother caught the plane to New York. – Serbocroatian

Date of birth is connected to where one is born - but age is connected to where one currently lives. See my answer. – Hegel

Answer 1

UTC is not a great choice for whole calendar dates such as a date of birth.

My date of birth is 1976-08-27. Not 1976-08-27T00:00:00Z.

• I currently live in the US Pacific time zone.

• My next birthday is from 2016-08-27T00:00:00-07:00 until 2016-08-28T00:00:00-07:00

• In UTC, that's equivalent to 2016-08-27T07:00:00Z until 2016-08-28T07:00:00Z

Of course, if I move to a different time zone before then, I'll celebrate my birthday over a completely different set of ranges.

• If I move to Japan, then my birthday will come 16 hours sooner.

• My next birthday would be from 2016-08-27T00:00:00+09:00 until 2016-08-28T00:00:00+09:00

• In UTC, that's equivalent to 2016-08-26T15:00:00Z until 2016-08-27T15:00:00Z

Therefore, a date of birth (or anniversary date, hire date, etc.) should be stored as a simple year, month and day. No time, and no time zone.

In MySQL, use the DATE type. Do not use DATETIME, TIMESTAMP or an integer containing Unix time.

Also consider that evaluation of age depends on the time zone where the person is currently located, not the time zone where they were born. If the person's location is unknown to the asker - then it's the asker's time zone that is relevant. "How old are you according to you?" is not necessarily the same as "How old are you according to me?".

Of course, where you live doesn't actually make you older or younger - but it comes down to how we as humans evaluate age in years based on our local calendars. If you were instead to ask "How many minutes old am I?" then answer depends on the instantaneous point in time where you were born - which could be measured in UTC, but will usually be given as a local time and time zone. However, in the common case, one does not usually collect that level of detail.

Hegel

Thanks for the great explanation, really helpfull! – Hathorn

Answer 2

Unix does not know that you are storing a birth date. It just knows that you are storing a timestamp in Unix format. The timestamp includes a time component.

When you convert from the birth date to the timestamp, and back from the timestamp to the birth date, you need to use consistent timezones in order to avoid a time difference in either direction.

Using UTC is a fine choice. The key though is consistency.

Corves