Question: Is ISO8601 the best date-format for PostgreSQL jsonb when i want to filter by the date?

Question

Is ISO8601 the best date-format for PostgreSQL jsonb when i want to filter by the date?

Answers 2
Added at 2016-11-16 09:11
Tags
Question

I'm new to PostgreSQL and I have the following question:

I have a table with just an id-column and a data-column, which uses the jsonb-type. Inside the jsonb-object I have a datetime field. I read in various posts, that I should use the ISO-8601 dateformat to store in the DB.

I want to filter my table by date like this:

SELECT * FROM table WHERE data->'date' > '2016-01-01T00:00'

Is this really the best date-format for this purpose?

Thanks in advance :)

Answers
nr: #1 dodano: 2016-11-16 09:11

The big advantage of that format is that string order corresponds to date order, so a comparison like the one you quote in your question would actually work as intended.

A second advantage is that a timestamp in that format can easily be converted to a PostgreSQL timestamp with time zone value, because the type input function understands this format.

I hope you are not dealing with dates “before Christ”, because it wouldn't work so easily with those.

nr: #2 dodano: 2016-11-16 09:11

IMHO Your query should produce

ERROR: operator does not exist: jsonb > timestamp with time zone

If I get it right. In case you change -> to ->> it should get a text value instead of jsonb field (which is also not comparable to timestamp). It should be smth like

SELECT * FROM table WHERE (data->>'date')::timestamptz > '2016-01-01T00:00' to work

Source Show
◀ Wstecz