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.
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
.
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)
| 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 |