Postgresql:使用jsonb字段在时间范围之间进行查询 [英] Postgresql: Query Between time range using jsonb field

问题描述

我有一个包含两个字段的表:

I have a table with two fields:

id(serial), data(jsonb)

在数据中，我有一个记录，该记录的Datetime字段存储为UNIX时间戳:

And into data I have records with a Datetime field stored as UNIX timestamps:

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

我正在尝试在范围之间进行查询:

I'm 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

消息

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

某事我做错了，请云有人帮助我吗?谢谢.

Something I'm doing wrong, please cloud somebody help me? Thanks.

推荐答案

->>运算符以text的形式返回JSON对象字段(请参见

The ->> operator returns an JSON object field as text (see here). You need to cast 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

这篇关于Postgresql:使用jsonb字段在时间范围之间进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！