Sqlite choose only previous day data?

Hi,

I have a Sqlite database eventually I want to show the last 1 days data on a chart.
But first I need to sort the date. Today as I write this it is Friday.

I have the following ..
SELECT *
FROM DATA
WHERE ID LIKE 'Speedfit - Temperature'
ORDER BY Unix_Time,datetime ('now','-1 day');

I want too only have data for Friday (today), but i get all my data of 859 readings from weeks ago ?

Where am I going wrong, I have spent 2 hours on the internet but cannot find the answer.

Thanks

You have to put the condition on the date in the Where clause, not in the Order By clause. That is logical because you want to select records Where the date is today.

maybe the following works for you:

SELECT *
FROM DATA
WHERE  DATE(Unix_Time, 'unixepoch') = DATE('now','-1 day');

or for 24hs...

SELECT *
FROM DATA
WHERE "Unix_Time" >= date('now', '-1 days') AND "Unix_Time" < date('now');

for situations like this, I find it best to get queries working outside of node-red & when you have a working query, transpose it into your flow.

Thanks guys for your help

It is now working in NodeRed,
For anyone reading this in the future. I have included a version that you would use as a query in an actual Sqlite editor. And the same code but formatted to work in NodeRed.

This works as a query in a Sqlite editor

SELECT *
FROM DATA
WHERE ID LIKE 'Speedfit - Humidity'
AND DATE(Unix_Time, 'unixepoch') >= DATE('now','-1 day');

Also here it is formatted to work in NodeRed ( you can cut and paste )

msg.topic =

" SELECT * FROM DATA WHERE ID LIKE "  +
" 'Speedfit - Humidity' "             +
" AND DATE (Unix_Time, 'unixepoch') >= DATE ( 'now' , '-1 days' )";

return msg;
1 Like

@questuk you can also format sql queries easily using the `templates node. Give it a try

Hi again,

How would I format that in a template node, I have tried many ways but keep getting ...
Error: SQLITE_ERROR: unrecognized token: "{"

I have removed the + from the original, is this correct ?

One way i tried ...

   SELECT * FROM DATA 

      {   WHERE ID LIKE
         'Garage - Humidity'
          AND DATE (Unix_Time, 'unixepoch') >= DATE ( 'now' , '-2 days' );
      }

return msg;

Here is a set of examples using sqlite with the sql statements built in a template node

[{"id":"e1accd93.dfea2","type":"sqlite","z":"27a0e6af.221aea","mydb":"2cf8ba2c.82d4ce","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":610,"y":440,"wires":[["f4f475a6.fa6ee8"]]},{"id":"be357672.0ff768","type":"inject","z":"27a0e6af.221aea","name":"Insert customer data","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":420,"wires":[["74aca195.99942"]]},{"id":"f4f475a6.fa6ee8","type":"debug","z":"27a0e6af.221aea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":440,"wires":[]},{"id":"74aca195.99942","type":"template","z":"27a0e6af.221aea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO customers\n\t(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)\nVALUES\n\t(1,\"Alfreds Futterkiste\",\"Maria Anders\",\"Obere Str. 57\",\"Berlin\",\"12209\",\"Germany\"),\n\t(2,\"Ana Trujillo Emparedados y helados\",\"Ana Trujillo\",\"Avda. de la Constituci√≥n 2222\",\"M√©xico D.F.\",\"05021\",\"Mexico\"),\n\t(3,\"Antonio Moreno Taquer√≠a\",\"Antonio Moreno\",\"Mataderos 2312\",\"M√©xico D.F.\",\"05023\",\"Mexico\"),\n\t(4,\"Around the Horn\",\"Thomas Hardy\",\"120 Hanover Sq.\",\"London\",\"WA1 1DP\",\"UK\"),\n\t(5,\"Berglunds snabbk√∂p\",\"Christina Berglund\",\"Berguvsv√§gen 8\",\"Lule√•\",\"S-958 22\",\"Sweden\"),\n\t(6,\"Blauer See Delikatessen\",\"Hanna Moos\",\"Forsterstr. 57\",\"Mannheim\",\"68306\",\"Germany\"),\n\t(7,\"Blondel p√®re et fils\",\"Fr√©d√©rique Citeaux\",\"24, place Kl√©ber\",\"Strasbourg\",\"67000\",\"France\"),\n\t(8,\"B√≥lido Comidas preparadas\",\"Mart√≠n Sommer\",\"C/ Araquil, 67\",\"Madrid\",\"28023\",\"Spain\"),\n\t(9,\"Bon app'\",\"Laurence Lebihans\",\"12, rue des Bouchers\",\"Marseille\",\"13008\",\"France\"),\n\t(10,\"Bottom-Dollar Marketse\",\"Elizabeth Lincoln\",\"23 Tsawassen Blvd.\",\"Tsawassen\",\"T2F 8M4\",\"Canada\"),\n\t(11,\"B's Beverages\",\"Victoria Ashworth\",\"Fauntleroy Circus\",\"London\",\"EC2 5NT\",\"UK\"),\n\t(12,\"Cactus Comidas para llevar\",\"Patricio Simpson\",\"Cerrito 333\",\"Buenos Aires\",\"1010\",\"Argentina\"),\n\t(13,\"Centro comercial Moctezuma\",\"Francisco Chang\",\"Sierras de Granada 9993\",\"M√©xico D.F.\",\"05022\",\"Mexico\"),\n\t(14,\"Chop-suey Chinese\",\"Yang Wang\",\"Hauptstr. 29\",\"Bern\",\"3012\",\"Switzerland\"),\n\t(15,\"Com√©rcio Mineiro\",\"Pedro Afonso\",\"Av. dos Lus√≠adas, 23\",\"S√£o Paulo\",\"05432-043\",\"Brazil\"),\n\t(16,\"Consolidated Holdings\",\"Elizabeth Brown\",\"Berkeley Gardens 12 Brewery \",\"London\",\"WX1 6LT\",\"UK\"),\n\t(17,\"Drachenblut Delikatessend\",\"Sven Ottlieb\",\"Walserweg 21\",\"Aachen\",\"52066\",\"Germany\"),\n\t(18,\"Du monde entier\",\"Janine Labrune\",\"67, rue des Cinquante Otages\",\"Nantes\",\"44000\",\"France\"),\n\t(19,\"Eastern Connection\",\"Ann Devon\",\"35 King George\",\"London\",\"WX3 6FW\",\"UK\"),\n\t(20,\"Ernst Handel\",\"Roland Mendel\",\"Kirchgasse 6\",\"Graz\",\"8010\",\"Austria\"),\n\t(21,\"Familia Arquibaldo\",\"Aria Cruz\",\"Rua Or√≥s, 92\",\"S√£o Paulo\",\"05442-030\",\"Brazil\"),\n\t(22,\"FISSA Fabrica Inter. Salchichas S.A.\",\"Diego Roel\",\"C/ Moralzarzal, 86\",\"Madrid\",\"28034\",\"Spain\"),\n\t(23,\"Folies gourmandes\",\"Martine Ranc√©\",\"184, chauss√©e de Tournai\",\"Lille\",\"59000\",\"France\"),\n\t(24,\"Folk och f√§ HB\",\"Maria Larsson\",\"√Ökergatan 24\",\"Br√§cke\",\"S-844 67\",\"Sweden\"),\n\t(25,\"Frankenversand\",\"Peter Franken\",\"Berliner Platz 43\",\"M√ľnchen\",\"80805\",\"Germany\"),\n\t(26,\"France restauration\",\"Carine Schmitt\",\"54, rue Royale\",\"Nantes\",\"44000\",\"France\"),\n\t(27,\"Franchi S.p.A.\",\"Paolo Accorti\",\"Via Monte Bianco 34\",\"Torino\",\"10100\",\"Italy\"),\n\t(28,\"Furia Bacalhau e Frutos do Mar\",\"Lino Rodriguez \",\"Jardim das rosas n. 32\",\"Lisboa\",\"1675\",\"Portugal\"),\n\t(29,\"Galer√≠a del gastr√≥nomo\",\"Eduardo Saavedra\",\"Rambla de Catalu√Īa, 23\",\"Barcelona\",\"08022\",\"Spain\"),\n\t(30,\"Godos Cocina T√≠pica\",\"Jos√© Pedro Freyre\",\"C/ Romero, 33\",\"Sevilla\",\"41101\",\"Spain\"),\n\t(31,\"Gourmet Lanchonetes\",\"Andr√© Fonseca\",\"Av. Brasil, 442\",\"Campinas\",\"04876-786\",\"Brazil\"),\n\t(32,\"Great Lakes Food Market\",\"Howard Snyder\",\"2732 Baker Blvd.\",\"Eugene\",\"97403\",\"USA\"),\n\t(33,\"GROSELLA-Restaurante\",\"Manuel Pereira\",\"5¬™ Ave. Los Palos Grandes\",\"Caracas\",\"1081\",\"Venezuela\"),\n\t(34,\"Hanari Carnes\",\"Mario Pontes\",\"Rua do Pa√ßo, 67\",\"Rio de Janeiro\",\"05454-876\",\"Brazil\"),\n\t(35,\"HILARI√ďN-Abastos\",\"Carlos Hern√°ndez\",\"Carrera 22 con Ave. Carlos Soublette #8-35\",\"San Crist√≥bal\",\"5022\",\"Venezuela\"),\n\t(36,\"Hungry Coyote Import Store\",\"Yoshi Latimer\",\"City Center Plaza 516 Main St.\",\"Elgin\",\"97827\",\"USA\"),\n\t(37,\"Hungry Owl All-Night Grocers\",\"Patricia McKenna\",\"8 Johnstown Road\",\"Cork\",\"\",\"Ireland\"),\n\t(38,\"Island Trading\",\"Helen Bennett\",\"Garden House Crowther Way\",\"Cowes\",\"PO31 7PJ\",\"UK\"),\n\t(39,\"K√∂niglich Essen\",\"Philip Cramer\",\"Maubelstr. 90\",\"Brandenburg\",\"14776\",\"Germany\"),\n\t(40,\"La corne d'abondance\",\"Daniel Tonini\",\"67, avenue de l'Europe\",\"Versailles\",\"78000\",\"France\"),\n\t(41,\"La maison d'Asie\",\"Annette Roulet\",\"1 rue Alsace-Lorraine\",\"Toulouse\",\"31000\",\"France\"),\n\t(42,\"Laughing Bacchus Wine Cellars\",\"Yoshi Tannamuri\",\"1900 Oak St.\",\"Vancouver\",\"V3F 2K1\",\"Canada\"),\n\t(43,\"Lazy K Kountry Store\",\"John Steel\",\"12 Orchestra Terrace\",\"Walla Walla\",\"99362\",\"USA\"),\n\t(44,\"Lehmanns Marktstand\",\"Renate Messner\",\"Magazinweg 7\",\"Frankfurt a.M. \",\"60528\",\"Germany\"),\n\t(45,\"Let's Stop N Shop\",\"Jaime Yorres\",\"87 Polk St. Suite 5\",\"San Francisco\",\"94117\",\"USA\"),\n\t(46,\"LILA-Supermercado\",\"Carlos Gonz√°lez\",\"Carrera 52 con Ave. Bol√≠var #65-98 Llano Largo\",\"Barquisimeto\",\"3508\",\"Venezuela\"),\n\t(47,\"LINO-Delicateses\",\"Felipe Izquierdo\",\"Ave. 5 de Mayo Porlamar\",\"I. de Margarita\",\"4980\",\"Venezuela\"),\n\t(48,\"Lonesome Pine Restaurant\",\"Fran Wilson\",\"89 Chiaroscuro Rd.\",\"Portland\",\"97219\",\"USA\"),\n\t(49,\"Magazzini Alimentari Riuniti\",\"Giovanni Rovelli\",\"Via Ludovico il Moro 22\",\"Bergamo\",\"24100\",\"Italy\"),\n\t(50,\"Maison Dewey\",\"Catherine Dewey\",\"Rue Joseph-Bens 532\",\"Bruxelles\",\"B-1180\",\"Belgium\"),\n\t(51,\"M√®re Paillarde\",\"Jean Fresni√®re\",\"43 rue St. Laurent\",\"Montr√©al\",\"H1J 1C3\",\"Canada\"),\n\t(52,\"Morgenstern Gesundkost\",\"Alexander Feuer\",\"Heerstr. 22\",\"Leipzig\",\"04179\",\"Germany\"),\n\t(53,\"North/South\",\"Simon Crowther\",\"South House 300 Queensbridge\",\"London\",\"SW7 1RZ\",\"UK\"),\n\t(54,\"Oc√©ano Atl√°ntico Ltda.\",\"Yvonne Moncada\",\"Ing. Gustavo Moncada 8585 Piso 20-A\",\"Buenos Aires\",\"1010\",\"Argentina\"),\n\t(55,\"Old World Delicatessen\",\"Rene Phillips\",\"2743 Bering St.\",\"Anchorage\",\"99508\",\"USA\"),\n\t(56,\"Ottilies K√§seladen\",\"Henriette Pfalzheim\",\"Mehrheimerstr. 369\",\"K√∂ln\",\"50739\",\"Germany\"),\n\t(57,\"Paris sp√©cialit√©s\",\"Marie Bertrand\",\"265, boulevard Charonne\",\"Paris\",\"75012\",\"France\"),\n\t(58,\"Pericles Comidas cl√°sicas\",\"Guillermo Fern√°ndez\",\"Calle Dr. Jorge Cash 321\",\"M√©xico D.F.\",\"05033\",\"Mexico\"),\n\t(59,\"Piccolo und mehr\",\"Georg Pipps\",\"Geislweg 14\",\"Salzburg\",\"5020\",\"Austria\"),\n\t(60,\"Princesa Isabel Vinhoss\",\"Isabel de Castro\",\"Estrada da sa√ļde n. 58\",\"Lisboa\",\"1756\",\"Portugal\"),\n\t(61,\"Que Del√≠cia\",\"Bernardo Batista\",\"Rua da Panificadora, 12\",\"Rio de Janeiro\",\"02389-673\",\"Brazil\"),\n\t(62,\"Queen Cozinha\",\"L√ļcia Carvalho\",\"Alameda dos Can√†rios, 891\",\"S√£o Paulo\",\"05487-020\",\"Brazil\"),\n\t(63,\"QUICK-Stop\",\"Horst Kloss\",\"Taucherstra√üe 10\",\"Cunewalde\",\"01307\",\"Germany\"),\n\t(64,\"Rancho grande\",\"Sergio Guti√©rrez\",\"Av. del Libertador 900\",\"Buenos Aires\",\"1010\",\"Argentina\"),\n\t(65,\"Rattlesnake Canyon Grocery\",\"Paula Wilson\",\"2817 Milton Dr.\",\"Albuquerque\",\"87110\",\"USA\"),\n\t(66,\"Reggiani Caseifici\",\"Maurizio Moroni\",\"Strada Provinciale 124\",\"Reggio Emilia\",\"42100\",\"Italy\"),\n\t(67,\"Ricardo Adocicados\",\"Janete Limeira\",\"Av. Copacabana, 267\",\"Rio de Janeiro\",\"02389-890\",\"Brazil\"),\n\t(68,\"Richter Supermarkt\",\"Michael Holz\",\"Grenzacherweg 237\",\"Gen√®ve\",\"1203\",\"Switzerland\"),\n\t(69,\"Romero y tomillo\",\"Alejandra Camino\",\"Gran V√≠a, 1\",\"Madrid\",\"28001\",\"Spain\"),\n\t(70,\"Sant√© Gourmet\",\"Jonas Bergulfsen\",\"Erling Skakkes gate 78\",\"Stavern\",\"4110\",\"Norway\"),\n\t(71,\"Save-a-lot Markets\",\"Jose Pavarotti\",\"187 Suffolk Ln.\",\"Boise\",\"83720\",\"USA\"),\n\t(72,\"Seven Seas Imports\",\"Hari Kumar\",\"90 Wadhurst Rd.\",\"London\",\"OX15 4NB\",\"UK\"),\n\t(73,\"Simons bistro\",\"Jytte Petersen\",\"Vinb√¶ltet 34\",\"K√łbenhavn\",\"1734\",\"Denmark\"),\n\t(74,\"Sp√©cialit√©s du monde\",\"Dominique Perrier\",\"25, rue Lauriston\",\"Paris\",\"75016\",\"France\"),\n\t(75,\"Split Rail Beer & Ale\",\"Art Braunschweiger\",\"P.O. Box 555\",\"Lander\",\"82520\",\"USA\"),\n\t(76,\"Supr√™mes d√©lices\",\"Pascale Cartrain\",\"Boulevard Tirou, 255\",\"Charleroi\",\"B-6000\",\"Belgium\"),\n\t(77,\"The Big Cheese\",\"Liz Nixon\",\"89 Jefferson Way Suite 2\",\"Portland\",\"97201\",\"USA\"),\n\t(78,\"The Cracker Box\",\"Liu Wong\",\"55 Grizzly Peak Rd.\",\"Butte\",\"59801\",\"USA\"),\n\t(79,\"Toms Spezialit√§ten\",\"Karin Josephs\",\"Luisenstr. 48\",\"M√ľnster\",\"44087\",\"Germany\"),\n\t(80,\"Tortuga Restaurante\",\"Miguel Angel Paolino\",\"Avda. Azteca 123\",\"M√©xico D.F.\",\"05033\",\"Mexico\"),\n\t(81,\"Tradi√ß√£o Hipermercados\",\"Anabela Domingues\",\"Av. In√™s de Castro, 414\",\"S√£o Paulo\",\"05634-030\",\"Brazil\"),\n\t(82,\"Trail's Head Gourmet Provisioners\",\"Helvetius Nagy\",\"722 DaVinci Blvd.\",\"Kirkland\",\"98034\",\"USA\"),\n\t(83,\"Vaffeljernet\",\"Palle Ibsen\",\"Smagsl√łget 45\",\"√Örhus\",\"8200\",\"Denmark\"),\n\t(84,\"Victuailles en stock\",\"Mary Saveley\",\"2, rue du Commerce\",\"Lyon\",\"69004\",\"France\"),\n\t(85,\"Vins et alcools Chevalier\",\"Paul Henriot\",\"59 rue de l'Abbaye\",\"Reims\",\"51100\",\"France\"),\n\t(86,\"Die Wandernde Kuh\",\"Rita M√ľller\",\"Adenauerallee 900\",\"Stuttgart\",\"70563\",\"Germany\"),\n\t(87,\"Wartian Herkku\",\"Pirkko Koskitalo\",\"Torikatu 38\",\"Oulu\",\"90110\",\"Finland\"),\n\t(88,\"Wellington Importadora\",\"Paula Parente\",\"Rua do Mercado, 12\",\"Resende\",\"08737-363\",\"Brazil\"),\n\t(89,\"White Clover Markets\",\"Karl Jablonski\",\"305 - 14th Ave. S. Suite 3B\",\"Seattle\",\"98128\",\"USA\"),\n\t(90,\"Wilman Kala\",\"Matti Karttunen\",\"Keskuskatu 45\",\"Helsinki\",\"21240\",\"Finland\"),\n\t(91,\"Wolski\",\"Zbyszek\",\"ul. Filtrowa 68\",\"Walla\",\"01-012\",\"Poland\" )\n\t","output":"str","x":380,"y":420,"wires":[["e1accd93.dfea2"]]},{"id":"55b7fd7d.56057c","type":"comment","z":"27a0e6af.221aea","name":"Customers table - sql examples","info":"","x":170,"y":320,"wires":[]},{"id":"135b4e1a.208a9a","type":"inject","z":"27a0e6af.221aea","name":"Create CUSTOMERS Table","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":170,"y":380,"wires":[["7c7f770a.eddd98"]]},{"id":"7c7f770a.eddd98","type":"template","z":"27a0e6af.221aea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE customers (\n    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,\n    CustomerName TEXT,\n    ContactName TEXT,\n    Address TEXT,\n    City TEXT,\n    PostalCode TEXT,\n    Country TEXT\n)","output":"str","x":380,"y":380,"wires":[["e1accd93.dfea2"]]},{"id":"5eac70b0.295a08","type":"inject","z":"27a0e6af.221aea","name":"select count","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":460,"wires":[["79d80752.19794"]]},{"id":"79d80752.19794","type":"template","z":"27a0e6af.221aea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT COUNT(*) from customers","output":"str","x":380,"y":460,"wires":[["e1accd93.dfea2"]]},{"id":"9ca23030.430eb","type":"inject","z":"27a0e6af.221aea","name":"select count by country","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":500,"wires":[["ca9528e0.b649f"]]},{"id":"ca9528e0.b649f","type":"template","z":"27a0e6af.221aea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT Country, COUNT(Country) from customers\nGROUP BY Country","output":"str","x":380,"y":500,"wires":[["e1accd93.dfea2"]]},{"id":"548d3419.0e18ec","type":"debug","z":"27a0e6af.221aea","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":610,"y":360,"wires":[]},{"id":"54136ab6.260004","type":"inject","z":"27a0e6af.221aea","name":"select * by country","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":540,"wires":[["1b823df8.355622"]]},{"id":"1b823df8.355622","type":"template","z":"27a0e6af.221aea","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * from customers\nORDER BY Country","output":"str","x":380,"y":540,"wires":[["e1accd93.dfea2"]]},{"id":"2cf8ba2c.82d4ce","type":"sqlitedb","z":"","db":"/Users/Paul/databases/hvac4.db","mode":"RWC"}]

Thanks for your help, got it working, much easier :grinning:

1 Like