I need a query which will help me to get data as described below
I have a table as followed
| ID | date | STATUS | TIME09_10 | TIME10_11 | TIME11_12 | TIME12_13 |
|---|---|---|---|---|---|---|
| 1 | 2021-09-01 | RUN | 30 | 60 | 45 | 0 |
| 2 | 2021-09-01 | WALK | 15 | 0 | o | 30 |
| 3 | 2021-09-01 | STOP | 15 | 0 | 15 | 30 |
I want this data to be in below format. From the above table , value columns name has to be replaced with hour and join with date column. I would really appreciate someone’s help. I am using postgres sql database.
| date & time | run | walk | stop |
|---|---|---|---|
| 2021-09-01 09:00 | 30 | 15 | 15 |
| 2021-09-01 10:00 | 60 | 0 | 0 |
| 2021-09-01 11:00 | 45 | 0 | 15 |
| 2021-09-01 12:00 | 0 | 30 | 30 |
You can use the crosstab(source_sql text, category_sql text) function. You need to install the tablefunc extension:
create extension if not exists tablefunc;
Read about the extension in the documentation.
The function expects data in the three-column format (row_name, category, value). In this case they are date+time, status and duration.
select
date+ '8 hour'::interval+ '1 hour'::interval* i as hour,
status,
(array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i
hour | status | duration
---------------------+--------+----------
2021-09-01 09:00:00 | RUN | 30
2021-09-01 09:00:00 | WALK | 15
2021-09-01 09:00:00 | STOP | 15
2021-09-01 10:00:00 | RUN | 60
2021-09-01 10:00:00 | WALK | 0
2021-09-01 10:00:00 | STOP | 0
2021-09-01 11:00:00 | RUN | 45
2021-09-01 11:00:00 | WALK | 0
2021-09-01 11:00:00 | STOP | 15
2021-09-01 12:00:00 | RUN | 0
2021-09-01 12:00:00 | WALK | 30
2021-09-01 12:00:00 | STOP | 30
(12 rows)
Pass the query as the first argument to the function:
select *
from crosstab(
$source$
select
date+ '8 hour'::interval+ '1 hour'::interval* i as hour,
status,
(array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i
$source$,
$category$
values('RUN'), ('STOP'), ('WALK')
$category$
) as (hour timestamp, run int, stop int, walk int)
hour | run | stop | walk
---------------------+-----+------+------
2021-09-01 09:00:00 | 30 | 15 | 15
2021-09-01 10:00:00 | 60 | 0 | 0
2021-09-01 11:00:00 | 45 | 15 | 0
2021-09-01 12:00:00 | 0 | 30 | 30
(4 rows)
There is a nice alternative if you do not want to use the extension. Convert the first query results to the expected output with the jsonb function:
select
hour,
(activities->>'RUN')::int as run,
(activities->>'STOP')::int as stop,
(activities->>'WALK')::int as walk
from (
select hour, jsonb_object_agg(status, duration) as activities
from (
select
date+ '8 hour'::interval+ '1 hour'::interval* i as hour,
status,
(array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i
) s
group by hour
) s
order by hour
Test the jsonb solution in Db<>fiddle.