CODEWITHSUNDEEP
postgresqlschemadate
Frank Farmer
Frank Farmer

Reputation: 39366

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.

Upvotes: 9

Views: 6114

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657942

Just store the date with an arbitrary leapyear and format it as needed in SELECTs. Like: to_char(birthday, 'DD.MM') 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 - 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.

Upvotes: 9

José A. Q. C. Gomes
José A. Q. C. Gomes

Reputation: 11

I ran into the same problem, and the solution I adopted was to create a composite type like "birthday" with the "day" and "month" columns as integers:

CREATE TYPE birthday AS (
    day integer,
    month integer
);

This way you can use literals like '(02,29)'::birthday. (I know it's not ideal).

We still have the validation problem, so my solution was to create a DOMAIN that performed this verification:

CREATE OR REPLACE FUNCTION public.check_birthday(
    birthday birthday)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    IMMUTABLE LEAKPROOF PARALLEL SAFE 
AS $BODY$
    DECLARE
        valid_date date;
    BEGIN
        valid_date := MAKE_DATE(
            2000, -- arbitrary leap year
            birthday.month,
            birthday.day
        );

        RETURN TRUE;

    EXCEPTION 
        WHEN datetime_field_overflow
        THEN RETURN FALSE;
    END;
$BODY$;
CREATE DOMAIN public.valid_birthday
    AS birthday CHECK (check_birthday(VALUE));

And optionally you can also create a Postgres CAST to simplify the conversion to DATE and vice versa:

CREATE OR REPLACE FUNCTION public.to_date(
        birthday birthday,
        year integer
    )
    RETURNS date
    LANGUAGE 'plpgsql'
    IMMUTABLE LEAKPROOF PARALLEL SAFE 
AS $BODY$
    BEGIN
        IF birthday.month = 2 and birthday.day = 29 THEN -- The leap year day exception case
            RETURN MAKE_DATE(year,3,1) - '1 day'::interval; -- Returns the date before 03/01 of given year
        ELSE
            RETURN MAKE_DATE(
                year,
                birthday.month,
                birthday.day
            );
        END IF;     
    END;
$BODY$;
CREATE OR REPLACE FUNCTION public.to_date(birthday birthday)
    RETURNS date LANGUAGE 'sql' IMMUTABLE LEAKPROOF PARALLEL SAFE 
AS $$
    SELECT to_date(
        birthday,
        extract(year from now())::integer
    )
$$;

CREATE CAST (birthday AS date)
    WITH FUNCTION public.to_date(birthday)
    AS IMPLICIT;

SELECT '(02,29)'::birthday::date; -- 2024-02-29
SELECT to_date('(02,29)'::birthday, 2023); -- 2023-02-28
SELECT your_birthday_column::date FROM your_table; -- Same effect

CREATE OR REPLACE FUNCTION public.to_birthday(birthdate date)
    RETURNS birthday LANGUAGE 'sql' IMMUTABLE LEAKPROOF PARALLEL SAFE 
AS $$
    SELECT row(
        extract(month from birthdate)::integer,
        extract(day from birthdate)::integer
    )::birthday
$$;
CREATE CAST (date AS birthday)
    WITH FUNCTION public.to_birthday(date )
    AS IMPLICIT;

SELECT '2024-02-29'::date::birthday; -- (02/29);

I know that this solution is very verbose and has a lot of low code, but for use in my implementations it was ideal and very robust. I hope it helps you :)

Upvotes: 1

arvind
arvind

Reputation: 1493

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.

Upvotes: -1

Greg Hewgill
Greg Hewgill

Reputation: 993941

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.

Upvotes: 3

Related Questions