add interval to timestamp with milis from jsonb

In DB, I have an array of objects

[{"effectiveTo":"1603939934019","effectiveFrom":1603896734146}]

I need to check if effectiveFrom is bigger than now – 7 days or effectiveFrom + 1 year is lower than effectiveTo I did 1st part but I’m a bit lost in second, there is a lot of casting and I still get cannot cast type timestamp without time zone to bigint error it appears in below condition:

(
    (value->>'effectiveFrom')::bigint > (extract('epoch' from now() - INTERVAL '7 DAYS')::bigint * 1000)  
    or (value->>'effectiveTo')::bigint < (extract('epoch'from((value ->>'effectiveFrom')::bigint * 1000)::timestamp + INTERVAL '1 YEAR'))::bigint
) 

Probably I missed something close to extract method. Unfortunately effectiveTo is a string, when effectiveFrom is a bigint, I’m not able to change it so keep it in mind

Answer

Since you need date arithmetics that cannot be performed directly on the unix timestamps (such as adding on year), I find that it is probably simpler to convert both values to timestamps, that you can then easily manipulate.

Assuming that you have a jsonb array in column js of table mytable, you could phrase the query as:

select v.*
from mytable t
cross join lateral jsonb_array_elements(t.js) j(obj)
cross join lateral (values(
    to_timestamp((obj ->> 'effectiveFrom')::bigint / 1000), 
    to_timestamp((obj ->> 'effectiveTo'  )::bigint / 1000)
) v(effective_from, effective_to)
where 
    v.effective_from > now() - interval '7 day'
    or (v.effective_to > v.effective_from + interval '1 year')