CODEWITHSUNDEEP
postgresqljsonb
anyavacy
anyavacy

Reputation: 1697

casting or converting string value into date in postgres/jsonb

I am trying to write a query that needs using the function MAX(). The data is stored in a jsonb column like: {"created_at": "2012-08-03T02:32:44", "company": "apple"}.

I would like to run the MAX() function on the 'created_at' key.

SELECT MAX(data -> 'created_at) FROM ... does not work.

SELECT MAX(cast(data -> 'created_at) as DATE) FROM ... does not work as well.

Upvotes: 5

Views: 12513

Answers (3)

achini
achini

Reputation: 449

you can use date(colName ->> 'created_at')

and you can use in WHERE conditions as well

date(colName ->> 'created_at') BETWEEN '2021-01-01' and '2022-01-01'

Upvotes: 9

Franz Noel
Franz Noel

Reputation: 1880

To convert it to a Date datatype, do this:

to_date(table.date_column->>'date', 'YYYY-MM-DD') AS date_namespace

To a Timestamp datatype

to_timestamp(table.date_column->>'date', 'YYYY-MM-DDTHH:MI:SS.MS') AS timestamp_namespace

This will allow you to use BETWEEN dates syntax like so:

WHERE table.date_column ->> 'date' BETWEEN '2021-10-01' AND '2021-10-30'

Upvotes: 0

anyavacy
anyavacy

Reputation: 1697

I have randomly tried another attempt and it worked

data ->> 'created_at' AS DATE works since ->> returns text

Upvotes: 6

Related Questions