Bits find use in DB Applications too!
We are providing applications for Equipment Maintenance (and more) on SAAS terms to large corporates who are into manufacturing.
All the factories have multiple workshops where some unit of the manufacturing is done in each workshop. Some workshops are active 7 days in a week, others inactive during the weekends, still others are closed in the middle of the week.
This information of ‘workshop activity days’ needs to be stored in a table in PostgreSQL DB in a way that can be queried easily and accurately for generating vital reports and there are a very large number of reports depending on this workshop activity information.
DB Support for Bit Level Operations
PostgreSQL provides good support for bit level operations but this feature is not tapped to its potential. Bitwise operations can be used for the various integer data types the DB supports and the bitwise operators are the same as for languages like C/C++/Java/Javascript.
The only operators we need for our purpose are:
- << – Left Shift Operator
- | – OR operator
- & – AND operator
Solution
Table Column Design
For each workshop we need to note the days of the week on which a workshop is active and store this information in a table column. To get the Day of Week for any date as an integer value, we use the function:
date_part(‘dow’, <any date>)::int
The function date_part(…) returns a double precision value
which we cast to an integer (::int).
The reason for seeking the ‘day of week’ as an integer value
will be clear as you read on.
We create a table workshop_info including the
following columns:
| Column Name | Data Type | Remarks |
|---|---|---|
| workshop_id | int | primary key column |
| workshop_descript | varchar(50) | this would be unique |
| workshop_days | int | workshop activity days in this column |
We will store the days of the week a workshop is active in a single integer value i.e. column workshop_days.
Populating the Table
Consider 2 workshops in the factory:
| ID | Description | Days Active |
|---|---|---|
| 1 | Heat Treatment | Mon, Tue, Thu, Fri, Sat |
| 2 | Plating | Sun, Mon, Thu, Fri |
Bit Manipulation:
The call to function date_part(‘dow’, <date value>)::int returns integer values in the range 0 – 6 where 0 corresponds to Sunday and 6 corresponds to Saturday. 16-Dec-2022 (the date of preparing this blog) happens to be a Friday. Running the following query in the DB returns 5:
select date_part(‘dow’, ‘2022-12-16’::date)::int => 5
If a workshop is active on Friday (5) this will be indicated by setting bit number 5 in column workshop_days (refer above for table description). To set bit number 5 we use the left shift operator as follows:
(1 << 5)
To insert a record for the workshop with ID 1 with the activity days set for (Mon, Tue, Thu, Fri, Sat) we do as follows:
insert into workshop_info (workshop_id, workshop_descript, workshop_days) values
(1, ‘Heat Treatment’, (1 << 1) | (1 << 2) | (1 << 4) | (1 << 5) | (1 << 6))
This will create a row with a value of 118 for column workshop_days (i.e. bits 1 (Mon), 2 (Tue), 4 (Thu), 5 (Fri) and 6 (Sat) set to 1 with all other bits being 0). The ‘|’ (OR operator) adds up the values of the individual (1 << N) expressions (we could have used ‘+’ in place of ‘|’).
Similarly, for workshop with ID 2 we set the value for workshop_days to 51 as given by: (1 << 0) | (1 << 1) | (1 << 4) | (1 << 5)
Reading and Using the Information:
There are many reports where the equipment usage hours are needed for a given period say ‘01-Apr-2022’ to ‘31-Oct-2022’ for all the equipment housed in various workshops. We will need to calculate the days of workshop activity in this period and multiply by the shift hours per day.
To know if a workshop is active on ‘22-Jul-2022’ we can query:
select workshop_id, case ((1 << date_part(‘dow’, ‘2022-07-22’::date)::int & workshop_days) when 0 then ‘NO’ else ‘YES’ end is_active from workshop_info … JOIN CONDITIONS etc.
Note: In actual practice, we would select the values as 0 in place of ‘NO’ and 1 in place of ‘YES’ so that we can group and sum the active days count for the entire period for each workshop.
Conclusion:
Consider some alternatives to the above approach like storing a boolean value for each day of the week and then reading the value for each day to check the active status. This would be extremely difficult and clumsy.
Using bit operations greatly simplifies by storing the activity in a single integer value and checking the active status is also easy and elegant apart from being fast and accurate.
Websites store user information about the user hobbies and interest. This information can also be stored compactly as bit flags.
