SELECT BY DATE with CURRENT_TIMESTAMP

(1) By uclabs on 2022-09-15 12:56:40 [source]

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.

(2.3) By Chris Locke (chrisjlocke1) on 2022-09-15 15:12:51 edited from 2.2 in reply to 1 [link] [source]

I assume CURRENT_TIMESTAMP is a TEXT field. If so, just put the date in single quotes.

select * from weather_data where CURRENT_TIMESTAMP >= '2022-09-15T00:00' and CURRENT_TIMESTAMP <= '2022-09-15T23:59';

That assumes the field CURRENT_TIMESTAMP contains a date though. Re-reading your post, it's hard to tell if it is or not as the result doesn't contain field headers.

You can check that by just using

select current_timestamp 
from weather_data

(3) By Stephan Beal (stephan) on 2022-09-15 15:30:31 in reply to 1 [link] [source]

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

Aside from the quoting problem Chris mentions, CURRENT_TIMESTAMP is a built-in special value which always resolves to the current time, so your query is fundamentally flawed. Try:

sqlite> select current_timestamp;
'2022-09-15 15:26:44'
sqlite> select current_timestamp;
'2022-09-15 15:26:05'

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

(4) By Holger J (holgerj) on 2022-09-16 12:06:24 in reply to 1 [link] [source]

The part of your query 

    CURRENT_TIMESTAMP >= 2022-09-15

actually compares the special value of the current timestamp (see explanation by stephan) to the value 2022 - 9 - 15, as 2022, 9 and 15 are numbers and - is the minus operator.

Whenever a value has to be sent to an SQL engine which isn't a number, it has to be sent as a string and therefore has to be enclosed in single quotes.