casting or converting string value into date in postgres/jsonb

Question

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.

-> just returns json. Did you try ->> which returns text? — Nick
– Nick, Commented Jun 5, 2016 at 17:45
yeah i have just tried it suddenly and it worked. thnks anyway — anyavacy
– anyavacy, Commented Jun 5, 2016 at 17:50
ok apparently it does not work. I do not get an error, but the result is false. min() and max() returns always the same message — anyavacy
– anyavacy, Commented Jun 5, 2016 at 18:24
When casting to a date you are throwing away the time part. max(cat(data ->>'created_at' as timestamp)) should work: sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9025 — user330315
– user330315, Commented Jun 5, 2016 at 18:26
Step through it. Make sure you are extracting what you want. Then make sure you are casting it correctly. Ensure you are passing into max() a date, timestamp, etc. — Nick
– Nick, Commented Jun 5, 2016 at 18:33

achini · Accepted Answer · 2022-01-11 05:44:46Z

9

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'

answered Jan 11, 2022 at 5:44

achini

4494 silver badges7 bronze badges

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

Comments

anyavacy · Accepted Answer · 2016-06-05 17:49:49Z

6

I have randomly tried another attempt and it worked

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

answered Jun 5, 2016 at 17:49

anyavacy

1,7177 gold badges26 silver badges43 bronze badges

1 Comment

raga Over a year ago

loved this one... you saved me.Cheers!

Franz Noel · Accepted Answer · 2021-12-24 01:25:17Z

0

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'

answered Dec 24, 2021 at 1:25

Franz Noel

1,9002 gold badges26 silver badges54 bronze badges

Collectives™ on Stack Overflow

casting or converting string value into date in postgres/jsonb

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related