How to compare time interval (duration) in Postgres jsonpath

Question

In the following example I have two objects on input. I want to select the one where duration between start and end is more that one day -> I expect object called threeDays to be returned.

How to write such condition in jsonpath?

start and end are date times.

I am using Postres 13.

SELECT jsonb_path_query_array_tz('[
  { "name" : "oneDay", 
    "start": "2021-07-02 00:00:00",                                     
    "end":   "2021-07-03 00:00:00"
  },
  { "name" : "threeDays",
    "start": "2021-07-15 00:00:00",                                     
    "ende":  "2021-07-18 00:00:00"
  }
]'::jsonb, '$[*] ? (
    @.end.datetime() - @.start.datetime() > 1day     <-----  ???
).name');

may be this could help : https://stackoverflow.com/questions/20272650/how-to-loop-over-json-arrays-in-postgresql-9-3 — Uday Yadav, Aug 30 '21 at 06:34
JSON Path doesn't know how to do date math (unless this is extension support by postgres). — gregsdennis, Aug 30 '21 at 06:47

score 1 · Answer 1 · answered Aug 30 '21 at 06:45

Here it is using jsonb_array_elements and ->> operator but w/o jsonpath.

select j
from jsonb_array_elements(
'[
  { "name" : "oneDay", 
    "start": "2021-07-02 00:00:00",                                     
    "end":   "2021-07-03 00:00:00"
  },
  { "name" : "threeDays",
    "start": "2021-07-15 00:00:00",                                     
    "end":  "2021-07-18 00:00:00"
  }
]'::jsonb) j
where (j ->> 'end')::timestamp - (j ->> 'start')::timestamp > interval '1 day';

maybe this is a way to go – Michal Foksa Aug 30 '21 at 11:17 — Michal Foksa, Aug 30 '21 at 11:17

How to compare time interval (duration) in Postgres jsonpath

1 Answers1