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;

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!

  • 2
    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 – a_horse_with_no_name Sep 29 '18 at 5:28
  • 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 Sep 29 '18 at 5:32
  • 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 – Jason Young Sep 29 '18 at 5:36
  • @a_horse_with_no_name If you flesh that out a bit, it could well be an answer. – John aka hot2use Sep 29 '18 at 12:25
  • 1
    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. – John aka hot2use Sep 29 '18 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.