Clever Geek Handbook

Postgresql: query between time range using jsonb field

I have a table with two fields:

id(serial), data(jsonb)

And in the data I have records with Datetime field stored as UNIX timestamps:

{"Device":132,"Datetime": 1434166552,...}

I am trying to query between ranges:

SELECT *
FROM trips
WHERE data->>'Datetime' BETWEEN
    EXTRACT(EPOCH FROM date '2014-04-01') AND
    EXTRACT(EPOCH FROM date '2014-04-15' + interval '1 day')
    AND id = 123

Message

ERROR:  operator does not exist: text >= double precision
LINE 3: WHERE data->>'Datetime' BETWEEN

Is there something I am doing wrong, please cloud, can anyone help me? Thank.

+3


source to share


1 answer


The operator ->>

returns a JSON object field as text

(see here ). You need to throw it:



SELECT *
FROM trips
WHERE (data->>'Datetime')::int 
  BETWEEN EXTRACT(EPOCH FROM date '2014-04-01') 
      AND EXTRACT(EPOCH FROM date '2014-04-15' + interval '1 day')
  AND id = 123
+4


source






More articles:


All Articles