Best way to store days of the week in a single column

Question

I have a notifications table where someone can set which days of the week to be notified:

sun mon tue wed thu fri sat

Now I could create these as individual columns of type bool.

Is there a clean of storing these in a single column?

I will need to query for this information like:

select * from notifications where start_date >= now and is_monday = true

Would making a distinct column just be wiser or some sort of integer/bit mask somehow? Not sure how this would be done in a db column and if this is a good idea or not.

Answer 1

You can create the enumerate type:

create type day_of_week as enum (
    'sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'
);

and use it as the column type, e.g.:

create table notifications (
    id serial primary key,
    event text,
    start_date date,
    day_of_week day_of_week
);

You query may look like this:

select * 
from notifications 
where start_date >= current_date
and day_of_week = 'mon'

Note that values of the type are ordered, so you can, e.g.:

select * 
from notifications 
where day_of_week between 'mon' and 'thu'

Use the function to_char() to get day_of_week from a date:

select to_char(current_date, 'dy')::day_of_week as dow

 dow 
-----
 sun
(1 row) 

---

Update. If there may be more notifications per event, use an array of day_of_week, example:

create table notifications (
    id serial primary key,
    event text,
    start_date date,
    days_of_week day_of_week[]
);

insert into notifications values
(default, 'some event', '2018-10-01', '{mon, wed}');

select * 
from notifications 
where current_date >= start_date
and 'mon' = any(days_of_week);