I don't understand how `preprocess_id`

was produced and how an actual hour was mapped to `arrive_timeday`

in your desire result. Any way, you can use a `CASE`

expression or a mapping table to map to the number.

Using `CASE`

expression

```
SELECT arrive_date, arrive_timeday, arrive_location
, jsonb_object_agg(thing_type, total_things)
FROM (
SELECT arrive_date
, arrive_timeday
, arrive_location
, thing_type
, sum(quantity) AS total_things
FROM (
SELECT date_trunc('day', arrive_date) AS arrive_date
, case
when extract(hour from arrive_date) in (5, 6, 7 , 8, 9) then 0
when extract(hour from arrive_date) in (10, 11) then 1
when extract(hour from arrive_date) in (12, 13, 14) then 2
when extract(hour from arrive_date) in (15, 16, 17 ,18 ,19, 20) then 4
when extract(hour from arrive_date) in (21, 22, 23, 0, 1, 2, 3, 4) then 8
end arrive_timeday
, arrive_location
, thing_type
, quantity
FROM inventory
) inv
GROUP BY arrive_date, arrive_timeday, arrive_location, thing_type
) sub
GROUP BY arrive_date, arrive_timeday, arrive_location
ORDER BY arrive_date, arrive_timeday, arrive_location;
```

Using mapping table:

Table creation:

```
CREATE TABLE hour_mapping (
hour_from integer NOT NULL,
hour_to integer NOT NULL,
timeday integer NOT NULL,
descpt character varying NOT NULL
);
```INSERT INTO hour_mapping (hour_from, hour_to, timeday, descpt) VALUES

(5, 9, 0, 'morning')

, (10, 11, 1, 'mid_morning')

, (12, 14, 2, 'midday')

, (15, 20, 4, 'evening')

, (21, 23, 8, 'night')

, (0, 4, 8, 'night')

;

Query

```
SELECT arrive_date, arrive_timeday, arrive_location
, jsonb_object_agg(thing_type, total_things)
FROM (
SELECT date_trunc('day', arrive_date) AS arrive_date
, m.timeday as arrive_timeday
, arrive_location
, thing_type
, sum(quantity) AS total_things
FROM inventory inv, hour_mapping m
WHERE extract(hour from inv.arrive_date) between m.hour_from and hour_to
GROUP BY arrive_date, arrive_timeday, arrive_location, thing_type
) sub
GROUP BY arrive_date, arrive_timeday, arrive_location
ORDER BY arrive_date, arrive_timeday, arrive_location;
```

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5e878c9fea252a97e3ea14f45d016c50