SQLite Forum

Select Last 24 hours
Login

Select Last 24 hours

(1) By anonymous on 2021-02-02 19:33:02 [link] [source]

('SELECT * FROM table_name', conn)

How can you add to this command so only data from the last 24 hours is selected?

(2) By Keith Medcalf (kmedcalf) on 2021-02-02 20:59:36 in reply to 1 [link] [source]

Add a where clause that only selects data from the last 24 hours.

(3) By anonymous on 2021-02-02 23:07:13 in reply to 1 [link] [source]

... where your_date_field >= datetime('now','-24 hours')

(4) By Ryan Smith (cuz) on 2021-02-02 23:11:38 in reply to 1 [link] [source]

I love Keith's answer - it's 100% correct and shares exactly as much info as the question does.

I'll be the Samaritan this time and offer the following:

  1. There is no way for the database to know at what physical World/Human/Clock time any data-row was added, so in that regard, there is absolutely no way of selecting data that are above or below any specific age.
  2. Unless... If you've added a column/field that recorded the exact time when the row was added or the recorded event happened, then you have provided a time-based reference by which one could filter using the WHERE clause that Keith mentioned.

Since you have not given any schema and not mentioned how you store data (and which data you store), we do not know if you have such a column or store such a reference, so we can have no idea whether you would be able to filter using it.

If I was to assume that your table "table_name" DOES have such a column, perhaps called "time_added" which contains the ISO8601 format supported by SQLite, such as would be given by the function "datetime('now')" or the like, then you can construct a query like this to give the result you need:

"SELECT * FROM table_name WHERE time_added > datetime('now','-24 hour');"

The data that you added to the table would need to either have a default value set to the current date-time, or have values supplied something like this:

INSERT INTO table_name(id,time_added,...etc...) VALUES
 (1, datetime('now'), ...etc...);

To understand what I did there you may need to read up on expressions and date-time handling in SQLite.

Next time, please be a lot more clear with your question, it will save you a lot of time waiting for someone to answer.

(5) By Larry Brasfield (LarryBrasfield) on 2021-02-02 23:38:34 in reply to 1 [link] [source]

Adding to Ryan's immediately clueful post: You really need to read, understand and heed the common wisdom on asking technical questions. A good starting place would be "How To Ask Questions The Smart Way" A web search on ' "how to ask" good "technical questions" ' will turn up some more. You will do yourself and those willing to help you a favor by putting some serious study and thought into your question posting.

(6) By anonymous on 2021-02-06 21:23:06 in reply to 4 [link] [source]

Thanks for replying.

I tried using the WHERE statement and it works if I use a raw unix time number. If the raw unix time number is assigned to a named variable and I use that with the WHERE statement it fails. Sqlite thinks the named variable is a table.

I tried datetime('now', '-24 hour') and it gives a syntax error.

Why doesn't this code work? Why does it think unix_time_1hr_ago is a table?

unix_time_now = time.time() unix_time_1hr_ago = unix_time_now-3600

c.execute('SELECT * FROM btc_price_table WHERE btc_price_timestamp > unix_time_1hr_ago') pprint.pprint(c.fetchall())

(7) By anonymous on 2021-02-06 21:24:21 in reply to 5 [link] [source]

You bet...I am new to coding and I do find myself struggling to word the question in a good way.

I'll take a look at the resources you provided, many thanks.

(8) By anonymous on 2021-02-06 21:31:22 in reply to 4 [link] [source]

Replying again quickly, I found the issue. The named variable needs to be added separated by a comma. I did not know that and that is what was missing.

This works:

unix_time_now = time.time() unix_time_1hr_ago = unix_time_now-3600 # 3600 seconds is an hour

values = (unix_time_1hr_ago,) c.execute('SELECT * FROM btc_price_table WHERE btc_price_timestamp > ?', values)

I need to read up on why you need to add another layer of variable with values = (unix_time_1hr_ago, )

If I replace values with unix_time_1hr_ago it fails.

I'll chalk it up to "this is how sqlite wants to hear it" so that's how I'll say it.

Thank for all the feedback here.

(9) By Keith Medcalf (kmedcalf) on 2021-02-07 01:44:08 in reply to 8 [source]

c.execute('SELECT * FROM btc_price_table WHERE btc_price_timestamp > ?', (time.time()-3600,))

The python sqlite3 wrapper requires that the bindings be a sequence (for positional parameters/lookup by index) or a dictionary (for named parameters/lookup by name).

(10) By Ryan Smith (cuz) on 2021-02-07 10:34:04 in reply to 6 [link] [source]

I tried datetime('now', '-24 hour') and it gives a syntax error.

That's improbable. Next time please give the full statements you use, especially since you have self-admitted to be new to it, it really helps us see where a potential problem might be.

The way I know the above quoted statement cannot be giving an error as-is, is if you simply do this Query:

SELECT datetime('now', '-24 hour');

you will see it is perfectly serviceable without any syntax errors.

This leads me to believe another part of the query was at fault. Here is the version that should work for you (unless I am again missing some fundamental difference with your specific setup):

SELECT * FROM btc_price_table WHERE btc_price_timestamp > datetime('now', '-24 hour');

So, assuming your language parser uses doubled-up quotes for escaping single quotes, your actual language statement might look like this:

c.execute('SELECT * FROM btc_price_table WHERE btc_price_timestamp > datetime(''now'', ''-24 hour'');')

If in stead it uses back-slash escaping, this might be the correct way:

c.execute('SELECT * FROM btc_price_table WHERE btc_price_timestamp > datetime(\'now\', \'-24 hour\');')

You get my drift - the resulting SQL is what is important and it must match the first statement above.

The '-24 hour' time-modifier can of course be replaced by many time-frames, such as '-1 hour' or '-30 minute' or '-1 month' etc. More info here: https://www.sqlite.org/lang_datefunc.html

If it doesn't work, please state exactly what is used and post the exact code you tried, someone here will real quick see what needs fixing for it to work.

Good luck!