12

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

Improve this question

1 Answer 1

Reset to default
36

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you @redneb ! It's the time zone that is the secret source that I couldn't figure out on my own :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.