CODEWITHSUNDEEP
sqljsonpostgresql
itsmewiththeface
itsmewiththeface

Reputation: 320

Escaping forward slash in PostgreSQL JSON

I have a JSON object with a timestamp:

{"Date/Time": "YYYY-MM-DD HH:MM:SS"}

I'm trying to query this directly and convert it to a date type (with YYYY-MM) like so:

SELECT
    to_char(DATE (data->>'Date/Time'), 'YYYY-MM') as datetime
FROM
    mytable;

assuming the column data holds the JSON object, I get the error: invalid input syntax for type date: "time"

Is this because of the key having a forward slash? If so, how do I escape it? (escaping the forward slash with a back slash like data->>'Date\/Time' doesn't search for the right key anymore)

Upvotes: 0

Views: 2242

Answers (1)

user330315
user330315

Reputation:

I am assuming that "YYYY-MM-DD HH:MM:SS" in your question is actually a real timestamp value like 2018-07-13 18:55:42 and not the characters you have shown.

to_char() expects a date or timestamp value and converts that to a string. You want to the other way: convert a string to a timestamp. For that you need to use to_timestamp()

to_timestamp('{"Date/Time": "2018-07-13 18:55:42"}'::json ->> 'Date/Time', 'YYYY-MM-DD HH24:MI:SS')

returns the value as an actual timestamp value. If you want a DATE rather than a timestamp you can use to_date() instead of to_timestamp()

If you want to format that so that only year and month are displayed, you can format that timestamp using to_char() 

to_char(to_timestamp('{"Date/Time": "2018-07-13 18:55:42"}'::json ->> 'Date/Time', 'YYYY-MM-DD HH24:MI:SS'), 
       'yyyy-mm') as datetime

Upvotes: 1

Related Questions