Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to store birthdays without a year part?

Similar question: Postgres birthdays selection

We're designing a new feature: we'll be storing the month and day parts of people's birthdays, but not the year. So a user born on christmas will have the birthday "12/25". The question is, how can we best represent this in postgres?

  1. The date column requires a year, and also rejects leapdays in non-leapyears. We could store all birthdays with an arbitrary leap year, e.g. '1972-12-25', but that'd be a bit of a kludge.
  2. We could use two int columns, one for month and one for year, but we'd lose pg's built in date checking entirely, and you could store the date '02-31'.
  3. We could use a single text column, but querying against this would not be pretty.

Are there any options we're missing? We're currently leaning toward a pair of integer columns.

Edit:

Why not just storing the birth date -which must be a valid date-, and go from there?

Privacy -- this is an optional feature, and we don't want to ask for more personal information than needed.

like image 410
Frank Farmer Avatar asked Oct 10 '11 23:10

Frank Farmer


People also ask

What datatype is the best choice to store birthday?

You should store the date as DATE. And the Locale as a varchar (PHP or Java).


1 Answers

Just store the date with an arbitrary leapyear and format it as need in SELECTs.
I have a number of cases where I do exactly that. It's so much easier than all the other ideas.

Update after question edit

If the year of birth is optional then a date has the additional advantage that you can store the year if you have it - in the same 4 bytes a date column needs. Use an arbitrary leapyear that is impossible otherwise for dates without a year. Like 2400 or 1804.

like image 175
Erwin Brandstetter Avatar answered Sep 16 '22 16:09

Erwin Brandstetter