insert sensor reading with received timestamp

diff --git a/nl_new.sql b/nl_new.sql
index e03f9a2..aa661ed 100644 (file)
--- a/nl_new.sql
+++ b/nl_new.sql
@@ -5,8 +5,13 @@ create temporary table eg_received as select _id,json->'received' as received fr
 
 select count(*) from nl_received where received not in (select received from eg_received) ;
 
-create table nl_new as select * from nl_received where received not in (select received from eg_received) ;
+create temporary table nl_new as select * from nl_received where received not in (select received from eg_received) ;
 
-select time,mac,addr,"nodeId",json,_id,sensor_type from nodelog where _id in (select _id from nl_new);
+select to_timestamp((json->>'received')::int8/1000), time,mac,addr,"nodeId",json,_id,sensor_type from nodelog where _id in (select _id from nl_new);
+
+select count(*) from nl_new ;
+
+begin;
+
+insert into eg5120 select to_timestamp((json->>'received')::int8/1000) as time,mac,addr,"nodeId",json,sensor_type from nodelog where _id in (select _id from nl_new);
 
---insert into eg5120 select time,mac,addr,"nodeId",json,sensor_type from nodelog where _id in (select _id from nl_new);