How to INSERT SQLite Timestamp in human date not UNIX timestamp form

I try to insert human date coupled with my data in table but always get error like this
Screenshot_2019-09-10%20Node-RED%20wichet%20trueddns%20com
my flow

[{"id":"df7c4ab5.c42d2","type":"inject","z":"9c4e2b4a.f3bf3","name":"CREATE EMETER TABLE","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":930,"y":900,"wires":[["72c01c7d.4f10e4"]]},{"id":"22d9db3.1c61e24","type":"inject","z":"9c4e2b4a.f3bf3","name":"INSERT INTO at 00:01","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"01 00 * * *","once":false,"onceDelay":0.1,"x":930,"y":940,"wires":[["cf620389.7858e"]]},{"id":"cf620389.7858e","type":"simpletime","z":"9c4e2b4a.f3bf3","name":"Get Time","x":1140,"y":960,"wires":[["a7b3b9e9.312b6"]]},{"id":"a7b3b9e9.312b6","type":"function","z":"9c4e2b4a.f3bf3","name":"","func":"var current = flow.get(\"current\")\nvar voltage = flow.get(\"voltage\")\nvar power = flow.get(\"power\")\nvar energy = flow.get(\"energy\")\nvar time = msg.mytimes;\nvar date = msg.myyear+\"-\"+msg.mymonthn+\"-\"+msg.mydom\n\nvar newMsg = {\n \"topic\": \"INSERT INTO emeter VALUES ( \" + date + \", \" + time + \", \" + voltage + \",\" + current + \", \" + power + \", \" + energy + \")\"\n}\n\nreturn newMsg;\n","outputs":1,"noerr":0,"x":1290,"y":960,"wires":[["d0b2364b.efb0c"]]},{"id":"72c01c7d.4f10e4","type":"function","z":"9c4e2b4a.f3bf3","name":"","func":"msg.topic = \"CREATE TABLE emeter( date DATE NOT NULL PRIMARY KEY, time TIME NOT NULL, voltage FLOAT NOT NULL, current FLOAT NOT NULL, power INT NOT NULL, energy INT NOT NULL)\"\nreturn msg;","outputs":1,"noerr":0,"x":1290,"y":920,"wires":[["d0b2364b.efb0c"]]},{"id":"d0b2364b.efb0c","type":"sqlite","z":"9c4e2b4a.f3bf3","mydb":"33e1f2a3.618cee","sqlquery":"msg.topic","sql":"","name":"Electricity Usage KM.10","x":1600,"y":960,"wires":[["a92960d4.3b0e4","3e15ae3d.150b0a"]]},{"id":"a92960d4.3b0e4","type":"debug","z":"9c4e2b4a.f3bf3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1850,"y":960,"wires":[]},{"id":"33e1f2a3.618cee","type":"sqlitedb","z":"","db":"/tmp/sqlite","mode":"RWC"}]

Thanks:)

You need to insert them as a string:

VALUES ( " + date + ", " + time + "...
VALUES ('" + date + "', '" + time + "'...
1 Like

It's work! Thanks a lot.:smiley:

Not storing timestamps in UNIX format will negate any aggregate time related queries. You should be aware of this.

2 Likes