I have an sql database from node red. The table of the database contains a column with javascript date.now() timestamps e.g. 1641154320892. Another column of the table contains temperature values. I'd like to select temperature values of a specific time period.
I tried the following code:
import sqlite3
conn = sqlite3.connect('/home/ktm/Developing/test/home_automation.db')
print ("Opened database successfully")
conn.row_factory = lambda cursor, row: row[0]
c = conn.cursor()
ids = c.execute('SELECT Buero_temp FROM home_automation WHERE Zeitstempel BETWEEN '2022-01-05' AND '2022-01-07';').fetchall()
for i in ids:
print (i)
Unfortunately, I get "SyntaxError: invalid syntax"
What am I doing wrong?
Firstly there is a syntax error.
A string in Python can be formed using both single and double quotes. If you want to use single quote within a string then outer string has to encapsulated with double quotes.
For eg:
my_string = "My string has 'single quotes'"
Now coming to your query:
As per your question the data stored in DB is in the form of timestamps, eg: 1641154320892. Javascript uses 13 digits timestamp.
So you should query with timestamps.
import sqlite3
def str_date_to_epoch(s):
print(s)
return int(datetime.strptime(s, '%Y-%m-%d').timestamp() * 1000)
conn = sqlite3.connect('/home/ktm/Developing/test/home_automation.db')
print("Opened database successfully")
conn.row_factory = lambda cursor, row: row[0]
c = conn.cursor()
statement = f"SELECT Buero_temp FROM home_automation WHERE Zeitstempel BETWEEN {str_date_to_epoch('2022-01-05')} AND {str_date_to_epoch('2022-01-07')};"
ids = c.execute(statement).fetchall()
for i in ids:
print(i)