Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table activity(activity_date,department_id)as
select d::date,1 from generate_series(now(),now()-'1 week'::interval,'-1 day')d;

create table department(id,hours_of_operation)as values
(1,'{
            "Friday": {
                "closeTime": "1970-01-01T17:00:00.000Z",
                "isOpen": true,
                "isOpen24Hours": false,
                "openTime": "1970-01-01T08:00:00.000Z"
            },
            "Monday": {
                "isOpen": false
            },
            "Saturday": {
                "isOpen": false
            },
            "Sunday": {
                "isOpen": false
            },
            "Thursday": {
                "closeTime": "1970-01-01T17:00:00.000Z",
                "isOpen": true,
                "isOpen24Hours": false,
                "openTime": "1970-01-01T08:00:00.000Z"
            },
            "Wednesday": {
                "closeTime": "1970-01-01T17:00:00.000Z",
                "isOpen": true,
                "isOpen24Hours": false,
                "openTime": "1970-01-01T08:00:00.000Z"
            },
            "Tuesday": {
                "closeTime": "1970-01-01T17:00:00.000Z",
                "isOpen": true,
                "isOpen24Hours": false,
                "openTime": "1970-01-01T08:00:00.000Z"
            }
        }'::jsonb);

--I have another table that associates some activity on a day with a department and I want to know if on the day that the activity happened is the department was open. So how do I query this jsonb field with the day of week of the date from the other table?

--I have tried something like:

SELECT 8



SELECT 1


select quote_literal(to_char(a.activity_date, 'Day'))
      ,a.activity_date
      ,a.department_id
      ,(d.hours_of_operation->to_char(a.activity_date, 'Day'))->>'isOpen' as isOpen
from activity a
join department d on a.department_id = d.id


quote_literal
activity_date
department_id
isopen


'Wednesday'
2024-04-24
1
true

'Tuesday  '
2024-04-23
1
null

'Monday   '
2024-04-22
1
null

'Sunday   '
2024-04-21
1
null

'Saturday '
2024-04-20
1
null

'Friday   '
2024-04-19
1
null

'Thursday '
2024-04-18
1
null

'Wednesday'
2024-04-17
1
true



SELECT 8


select quote_literal(trim(to_char(a.activity_date, 'Day')))
      ,a.activity_date
      ,a.department_id
      ,(d.hours_of_operation->trim(to_char(a.activity_date, 'Day')))->>'isOpen' as isOpen
from activity a
join department d on a.department_id = d.id


quote_literal
activity_date
department_id
isopen


'Wednesday'
2024-04-24
1
true

'Tuesday'
2024-04-23
1
true

'Monday'
2024-04-22
1
false

'Sunday'
2024-04-21
1
false

'Saturday'
2024-04-20
1
false

'Friday'
2024-04-19
1
true

'Thursday'
2024-04-18
1
true

'Wednesday'
2024-04-17
1
true



SELECT 8


select quote_literal(to_char(a.activity_date, 'FMDay'))
      ,a.activity_date
      ,a.department_id
      ,(d.hours_of_operation->to_char(a.activity_date, 'FMDay'))->>'isOpen' as isOpen
from activity a
join department d on a.department_id = d.id


quote_literal
activity_date
department_id
isopen


'Wednesday'
2024-04-24
1
true

'Tuesday'
2024-04-23
1
true

'Monday'
2024-04-22
1
false

'Sunday'
2024-04-21
1
false

'Saturday'
2024-04-20
1
false

'Friday'
2024-04-19
1
true

'Thursday'
2024-04-18
1
true

'Wednesday'
2024-04-17
1
true



SELECT 8
quote_literal	activity_date	department_id	isopen
'Wednesday'	2024-04-24	1	true
'Tuesday '	2024-04-23	1	null
'Monday '	2024-04-22	1	null
'Sunday '	2024-04-21	1	null
'Saturday '	2024-04-20	1	null
'Friday '	2024-04-19	1	null
'Thursday '	2024-04-18	1	null
'Wednesday'	2024-04-17	1	true