How to query date field of json object in PostgreSQL 9.3?

Question

imagine a PostgreSQL 9.3 with a table that has an sql column that contains an object:

{"start":"2016-02-04 00:45:56.000"}

How could I query it to get objects with start date > :"2016-01-01 00:00:00.000"?

This question is specificly for version 9.3

Answer 1

Something like this should do it:

SELECT *
FROM mytable
WHERE (c->>'start')::timestamp with time zone > '2016-01-01'::timestamp with time zone;

I assume that the table is called mytable and the JSON column is called c. This also assumes that the times are in UTC. If your time is in local time, use timestamp without time zone instead.