1

I have the following query:

SELECT id, start_date::TIME, occurrence->0->>'startsOn'  FROM service WHERE name='A.F';

Which return:

id  | start_date |            ?column?             
------+------------+---------------------------------
 1573 | 18:00:00   | Mon, 29 Jun 2015 18:00:00 +0000
 1592 | 10:00:00   | Wed, 24 Jun 2015 10:00:00 +0000
 1605 | 18:00:00   | Thu, 25 Jun 2015 18:00:00 +0000
 1571 | 10:00:00   | Mon, 29 Jun 2015 10:00:00 +0000
 1591 | 20:15:00   | Tue, 30 Jun 2015 20:15:00 +0000
 1578 | 18:00:00   | Mon, 29 Jun 2015 20:00:00 +0000
 1620 | 12:00:00   | Sat, 27 Jun 2015 12:00:00 +0000
(7 rows)

what I am trying to do is convert occurrence->0->>'startsOn' to time, so the expected result should be:

  id  | start_date |            ?column?             
------+------------+---------------------------------
 1573 | 18:00:00   | 18:00:00
 1592 | 10:00:00   | 10:00:00
 1605 | 18:00:00   | 18:00:00
 1571 | 10:00:00   | 10:00:00
 1591 | 20:15:00   | 20:15:00
 1578 | 18:00:00   | 20:00:00
 1620 | 12:00:00   | 12:00:00

i tried the following:

SELECT id, start_date::TIME, occurrence->0->>'startsOn'::TIME  FROM service WHERE name='A.F';

But it is not working as it gives me the following syntax error:
ERROR:  invalid input syntax for type time: "startsOn"
1
  • 2
    brackets?.. (occurrence->0->>'startsOn')::TIME Commented Jun 13, 2016 at 14:20

1 Answer 1

1
select ('[{"startsOn":"Mon, 29 Jun 2015 18:00:00 +0000"}]'::json->0->>'startsOn')::timestamp::time

I did not have column "occurrence" so I mocked it up from your output

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.