Filter timestamp from sql database and extract as a list in python-CodePudding

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?

CodePudding user response：

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)