current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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.

share|improve this question
1  
-> just returns json. Did you try ->> which returns text? – Nicarus 9 hours ago
    
yeah i have just tried it suddenly and it worked. thnks anyway – anyavacy 9 hours ago
    
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 8 hours ago
    
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 – a_horse_with_no_name 8 hours ago
    
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. – Nicarus 8 hours ago
up vote 0 down vote

I have randomly tried another attempt and it worked

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.