How to efficiently store 672 bits of information in PostgreSQL

Question

I have a requirement that needs to store lots of bits and it's actually a time setting for every 15 minutes in a week (indicating if a user is free during that time).

So for one day we need: 60 minutes/ 15 = 4 * 24 hours = 96 bit

And for a week, it will be: 96 * 7 = 672

I was originally thinking that I could store this in 7 columns of bigint[2], each column representing a day and the first bigint of the array represening the bit from 00:00 to 12:00 (12 * 4 = 48bit).

And here is how I calculate the bigint:

 start_idx := extract(EPOCH FROM (first_half[1]::time - '00:00'::time))::integer/60/15;
 end_idx := extract(EPOCH FROM (first_half[2]::time - '00:00'::time))::integer/60/15;
 -- RAISE NOTICE 'idx % %', start_idx, end_idx;
 WHILE start_idx < end_idx LOOP
 first_r := first_r | (1::bigint << start_idx);
 start_idx = start_idx + 1;
 END LOOP;

So the start_idx will be the result and contains all the bits from 00:00 to 12:00.

But this is ugly because I have to separate the time setting into two parts and I just come across this thread that says that I should store the bits separately.

So I am not sure if I should use the array or if I should just create 672 columns of Boolean which I think is a bit crazy!

• user1822: bit(672) maybe? Or an array of boolean? A boolean[672] will be a lot bigger than a bit(672) if that is a problem depends on how many rows you will have
• Jason Young: bit(672) sounds good, I am thinking maybe 7 column of bit(96) is even better with bloom index. because of it more readable. but I am not sure about the performance
• Jason Young: 1M rows, I guess storage should be fine, the problem is the query, giving a free time setting query1, I need to find out all the people who're free during that time
• John K. N.: @a_horse_with_no_name If you flesh that out a bit, it could well be an answer.
• John K. N.: What are your requirements for retrieving/querying the data? Storing the time slots is only one part of the equation, retrieving the data could well be another issue.