Postgres jsonb cast recordset from UNIX to timestamp

Question

I'm working in a Postgres table that has a jsonb column. I've been able to create a recordset to turn the json to rows from the jsonb object. I'm struggling to convert timestamp from UNIX to readable timestamp.

This is what the jsonb object looks like with timestamp stored as UNIX:

{
  "signal": [
    {
      "id": "e80",
      "on": true,
      "unit": "sample 1",
      "timestamp": 1521505355
    },
    {
      "id": "97d",
      "on": false,
      "unit": "sample 2",
      "timestamp": 1521654433
    },
    {
      "id": "97d",
      "on": false,
      "unit": "sample 3",
      "timestamp": 1521654433
    }
  ]
}

ideally i'd like it to look like this but get an error for the timestamp

id | on   |  unit    | timestamp
---+------+----------+-------------------------- 
e80|true  | sample 1 | 2018-03-20 00:22:35+00:00
97d|false | sample 2 | 2018-03-21 17:47:13+00:00
97d|false | sample 3 | 2018-03-21 17:47:13+00:00

this is what i have so far which returns the expected values for the columns but gives an error for the timestamp column

select b.*
from device d
cross join lateral jsonb_to_recordset(d.events->'signal') as 
    b("id" integer, "on" boolean, "unit" text, "timestamp" timestamp)

the timestamp datatype is throwing off an error.

[22008] ERROR: date/time field value out of range

Any help or suggestions for casting the timestamp from UNIX to an actual timestamp is greatly appreciated.

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-03-25T06:32:06

You may specify it as INTEGER in column definition list and then Convert it to TIMESTAMP using TO_TIMESTAMP

Furthermore, Theid which you are trying to define can't be integer.

SQL Fiddle

Query 1:

SELECT b.id
    ,b.ON
    ,b.unit
    ,to_timestamp("timestamp") AS "timestamp"
FROM device d
CROSS JOIN lateral jsonb_to_recordset(d.events -> 'signal')
AS b("id" TEXT, "on" boolean, "unit" TEXT, "timestamp" INT)

Results:

|  id |    on |     unit |            timestamp |
|-----|-------|----------|----------------------|
| e80 |  true | sample 1 | 2018-03-20T00:22:35Z |
| 97d | false | sample 2 | 2018-03-21T17:47:13Z |
| 97d | false | sample 3 | 2018-03-21T17:47:13Z |

Postgres jsonb cast recordset from UNIX to timestamp

1 Answers