DATETIME in Sqlite3

OUTLOOK-UC 0
Viewed 67

I have a Python3 script that gets weather data from Openweathermap and inserts the data each hour in a Sqlite3 table.

def save_data_to_db(data):
    con = sqlite3.connect(DB_PATH)
    con.isolation_level = None
    cur = con.cursor()

    query = '''INSERT INTO weather_data VALUES (?, ?, ?, ?, ?, ?, ?, ?,  ?,?, ?, ?, ?, ?, ?, ?, ?, NULL)'''

    cur.execute(query, tuple(data[:-2:] + [datetime.datetime.now().isoformat()]))

    con.commit()
    con.close()

Current Code:

It inserts the DATETIME into a column CURRENT_TIMESTAMP.

Now when I run a SELECT STATEMENT to SELECT BY DATE it returns all rows no matter what or no rows. This is some of the statements I have tried:

sqlite> select * from weather_data where CURRENT_TIMESTAMP >= 2022-09-15; Clouds|few clouds|02d|91.47|99.64|85.86|93.97|1012.0|54.0|6.91||60.0|20.0||1663157922.0|1663202355.0|2022-09-14T12:29:13.932893| Clouds|few clouds|02d|92.26|100.83|89.33|93.97|1012.0|53.0|6.91||60.0|20.0||1663157922.0|1663202355.0|2022-09-14T12:51:05.048334| Clouds|few clouds|02d|87.17|95.14|76.73|91.22|1011.0|64.0|6.91||60.0|20.0||1663157922.0|1663202355.0|2022-09-14T17:00:02.352772|

RETURNS NOTHING

select * from weather_data where CURRENT_TIMESTAMP >= 2022-09-15 and CURRENT_TIMESTAMP <= 2022-09-15; sqlite>

RETURNS EVERYTHING: sqlite> SELECT * FROM weather_data WHERE CURRENT_TIMESTAMP BETWEEN datetime('now') AND datetime('now','+1 day','-0.001 second'); Clouds|few clouds|02d|91.47|99.64|85.86|93.97|1012.0|54.0|6.91||60.0|20.0||1663157922.0|1663202355.0|2022-09-14T12:29:13.932893| Clouds|few clouds|02d|92.26|100.83|89.33|93.97|1012.0|53.0|6.91||60.0|20.0||1663157922.0|1663202355.0|2022-09-14T12:51:05.048334|

I tried to modify the script to this:

def save_data_to_db(data):
    con = sqlite3.connect(DB_PATH)
    con.isolation_level = None
    cur = con.cursor()

    query = '''INSERT INTO weather_data VALUES (?, ?, ?, ?, ?, ?, ?, ?,  ?,?, ?, ?, ?, ?, ?, ?, ?, NULL)'''

    cur.execute(query, tuple(data[:-2:] + [datetime.datetime.now()]))

    con.commit()
    con.close()

This is what the new data looks like:

Clear|clear sky|01n|74.89|76.32|71.33|75.97|1009.0|90.0|4.52|7.11|72.0|1.0||1663244345.0|1663288687.0|2022-09-15 06:00:22.922304| sqlite>

But no change in SELECT RESULTS.

Looking for a way to select data by DATE RANGE and would like to get just a single day.

Any suggestions would be great as I have tried everything I can find.

sqlite
1 Answers

If you have a field named current_timestamp then you need to quote that field in your queries using either double-quoted or square brackets:

sqlite> select * from weather_data where "CURRENT_TIMESTAMP" >= '2022-09-15';

That will let sqlite know that you mean an identifier named current_timestamp and it will lose its special-value status:

sqlite> select "current_timestamp";
Parse error: no such column: current_timestamp
  select "current_timestamp";
         ^--- error here

--

OUTLOOK-UC0
Related