9

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.

Community
  • 1
  • 1
Frank Farmer
  • 38,246
  • 12
  • 71
  • 89

3 Answers3

8

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    Storing it as 2400 or 1804 could cause problems if you (accidentally?) convert it to a Unix-style timestamp. 1972 doesn't have that problem, but it has the drawback that it could easily be a valid birthdate. 2036 avoids both issues, but only until you start getting people born in 2036 in your database. Maybe use 1972 plus a flag telling you whether the year is valid? – Keith Thompson Oct 11 '11 at 00:26
  • @KeithThompson Yeah, good point. Really depends on what you need. Think before you act. – Erwin Brandstetter Oct 11 '11 at 00:39
3

Since the usual Postgres date functions won't really help you anyway, it seems best to store month and day as integer columns. You won't be able to "validate" the date any more than checking that the day doesn't exceed the maximum number of days in the given month (if the user enters 29 Feb, you can't really argue with them).

If you're concerned about validity of the data, you could have the month/day pair be a foreign key into a table that stores 366 rows, one for each valid month/day pair.

You may still need to handle 29 Feb specially if you're doing something like sending birthday greetings to a user.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
0

This is more of a client-side validation.

We used to offer users a list of months and days.

Check it before form submission.

Store it in a table as a single string column, which is indexed.

Each day , a cron job , scans all valid entries falling as todays day/month combination and post greetings to their mailbox as per type of occassion like (Anniversary or birthdate) etc.

arvind
  • 1,385
  • 1
  • 13
  • 21