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

Based on the question, I could infer that the requirement is to store days of the week and utilize them. For this, I recommend having a single column of INT type.

The idea of binary to decimal conversion can be used to store integer values. For this, a 7-bit equivalent number can be used.

For example,

Case 1: 0000000 - This will be equivalent to 'No days'.

Case 2: 0000001 - This will be equivalent to 'Monday only'

Case 3: 0000011 - This will be equivalent to 'Monday and Tuesday'

Case 4: 1010101 - This will be equivalent to 'Monday, Wednesday, Friday and Sunday'.

The right-most bit can be tagged to any day (say Monday) and the bits to the left represent the successive days. In the example above, bit 0 represents Monday, bit 1 represents Tuesday, and so on.

Now, the case 1 binary, when converted to decimal means, its decimal equivalent is 0. Case 2 decimal equivalent is 1 and so on.

So, If it is Monday only, 1 can be stored in the database column. The maximum value will be 127 ( Binary - 1111111 ).

In the backend code, logic can be written to convert binary to decimal and store it in DB. Conversion from decimal to binary can be used to retrieve the days of week information.

Answer 2

tl;dr

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

SELECT * 
FROM event_ 
WHERE 
    when_ >= DATE '2018-01-22' 
    AND
    EXTRACT( isodow FROM when_ )  -- Access portion of date-value in the `isodow` field meaning the day-of-week as defined by the ISO 8601 standard.
    = 2                           -- 2 = Tuesday in ISO 8601 definition of a week.
;

If you want to check for multiple day-of-week values, use an array.

SELECT * 
FROM event_ 
WHERE 
    when_ >= DATE '2018-01-22' 
    AND
    EXTRACT( isodow FROM when_ )  -- Access portion of date-value in the `isodow` field meaning the day-of-week as defined by the ISO 8601 standard.
    = 
    ANY( ARRAY[ 2 , 3 , 4 ] )     -- Define numbers for day-of-week per ISO 8601 where Monday-Sunday is 1-7. 
;

Details

Best way to store days of the week

Don’t.

As suggested in Answer by Barbaros Özhan, storing the day-of-week in addition to the date is redundant and therefore violates normalization.

Postgres offers the EXTRACT function to return a value (a “field”) representing some aspect of a date-time value. The Ingres-style equivalent is also available, date_part.

Postgres offers two fields to determine the day-of-week:

• isodow
  Returns 1-7 for the day-of-week Sunday-Monday as defined by the ISO 8601 standard.

• dow
  Returns 0-6 for the day-of-week Sunday-Saturday as defines a week in some places such as the United States.

I suggest you stick with the first, the standard definition.

Let's put those to use.

First, get a date.

SELECT DATE '2018-01-23' ;

2018-01-23

Get the day-of-week for that date.

SELECT EXTRACT ( isodow FROM DATE '2018-01-23' ) ;

Returns a double number value, 2, meaning Tuesday.

2

Define your target day-of-week values in an array. For example, Tuesday-Wednesday-Thursday, 2-4 in ISO 8601 day-of-week numbering.

ARRAY[ 2 , 3 , 4 ]

Ask if the array contains a certain number.

SELECT ARRAY[ 2 , 3 , 4 ] @> ARRAY[ 2 ] ;

true

Or, another approach to the same result.

SELECT 2 = ANY ( ARRAY[ 2 , 3 , 4 ] ) ;

true

Apply a certain date in that statement.

SELECT EXTRACT ( isodow FROM DATE '2018-01-23' )  = ANY ( ARRAY[ 2 , 3 , 4 ] ) ;

true

We can put that all together. We will create a table, insert 3 rows, and do a query. We expect our 2nd & 3rd rows to be found for ISO 8601 day-of-week 2-4, whereas the 1st row for the 22nd of January is a Monday (day # 1) and should be omitted.

DROP TABLE IF EXISTS event_
;

CREATE TABLE IF NOT EXISTS event_ (
    pkey_       INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY ,
    when_       DATE NOT NULL
);

INSERT INTO event_ ( when_ ) 
VALUES ( DATE '2018-01-22' ) , ( DATE '2018-01-23' ) , ( DATE '2018-01-24' )
;

SELECT * FROM event_ 
WHERE EXTRACT( isodow FROM when_ ) = ANY( ARRAY[ 2 , 3 , 4 ] )
;

When run, sure enough, the first row is omitted.

2 "2018-01-23"

3 "2018-01-24"

Used pgAdmin 3.5 for this demo with PostgreSQL 11.0 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit.

Storing list of day-of-week values

Your Question is not quite clear.

Perhaps you were also asking how to store which of the seven day-of-week values are the preference of each user.

First define an enum for the seven day-of-week values. This provides type-safety and ensures valid values.

CREATE TYPE ISO_DOW_ AS ENUM ( 
    'MONDAY' , 'TUESDAY' , 'WEDNESDAY' , 'THURSDAY' , 'FRIDAY' , 'SATURDAY' , 'SUNDAY' 
) ;

Define a table to store the user preferences. We add a column of type array, an array of integers.

CREATE TABLE user_pref_ (
    pkey_       INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY ,
    dows_       ISO_DOW_[] NOT NULL -- User can select 0, 1, or up to 7 day-of-week values to be used for searching notifications.
) ;

Be sure to use the nifty COMMENT feature in Postgres to document our intent. I'll leave that as an exercise for the reader.

Insert some rows.

INSERT INTO user_pref_ ( dows_ )
VALUES ( ARRAY[]::ISO_DOW_[] ) , ( ARRAY[ 'TUESDAY' , 'WEDNESDAY' , 'THURSDAY' ]::ISO_DOW_[] ) , ( ARRAY[ 'FRIDAY' ]::ISO_DOW_[] )
;

The last step, I cannot quite complete. I have not yet determined a way to retrieve an array in a subquery to be passed to the ANY function of our outer query. Should be something like this:

SELECT * FROM event_
WHERE EXTRACT( isodow FROM when_ ) = ANY(
    SELECT dows_ FROM user_pref_ WHERE pkey_ = 2 ;
) ;

Answer 3

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);

Answer 4

I don't think you need to store another column but use a collation that depends on start_date column and your day parameter as sun or mon or tue ... etc.

with notifications(start_date) as
(
  select date'2018-11-04'      
)
select *
  from notifications
 where start_date <= now() 
   and substring(to_char(start_date, 'day'),1,3) = 'sun' -- 'mon', 'tue' ...

Rextester Demo