Converting values which were incorrectly treated as binary, instead of BCD

bcd_to_decimal_conversion.sql

select
(SUBSTRING(binary_num, 1, 4)::bit(4)::int::varchar ||
 SUBSTRING(binary_num, 5, 4)::bit(4)::int::varchar ||
 SUBSTRING(binary_num, 9, 4)::bit(4)::int::varchar ||
 SUBSTRING(binary_num, 13, 4)::bit(4)::int::varchar ||
 SUBSTRING(binary_num, 17, 4)::bit(4)::int::varchar)::int * 10 as new_state
, state_id, * from (
  select right((state::int / 10)::bit(32)::varchar, 20) as binary_num, *
  from states
  where entity_id = 'sensor.water_meter'
  and state::int > 1000000
  and state not in ('unknown', 'unavailable')
) q1