CODEWITHSUNDEEP
sqlpostgresqlcasting
Kodin
Kodin

Reputation: 190

PostgreSQL - Casting JSON string to INTERVAL returns incorrect value

I am observing a weird behaviour on Postgres server when casting a JSON string into INTERVAL type. I tried it out on Postgres v9, v10, v11 and got the same results on each.

Example:

select '{"minutes": 20, "hours":10}'::interval;

+----------+
| interval |
+----------+
| 20:00:10 |
+----------+
(1 row)

No errors, warnings, nothing. Postgres does not complain but gives back an incorrect result. Why does it behave like this?

On the other hand, these examples return error:

select '{}'::interval;

ERROR:  invalid input syntax for type interval: "{}"
LINE 1: select '{}'::interval;
               ^

select '{"blahblah": 10}'::interval;

ERROR:  invalid input syntax for type interval: "{"blahblah": 10}"
LINE 1: select '{"blahblah": 10}'::interval;
               ^

I know how to mitigate the issue. Here I just want to understand why it behaves like this.

Upvotes: 0

Views: 432

Answers (1)

Kodin
Kodin

Reputation: 190

To answer my own question. It looks like Postgres is simply parsing the text in a following way:

{"minutes": 20, "hours":10}

20 is followed by hours so it becomes 20 hours. 10 is not followed by anything so it becomes 10 seconds. Final result is 20:00:10.

Another example: select '{hours 20 !!!!! d 10 ::: s}'::interval;

20 is followed by d and becomes 20 days. 10 is followed by s and becomes 10 seconds. Final result is 20 days 00:00:10.

Upvotes: 2

Related Questions