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?
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.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'
.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.
You should store the date as DATE. And the Locale as a varchar (PHP or Java).
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With