How to get Last 3 hours data from SQLite

Question

I am inserting data in table with current system time like System.currentTimeMillis(). So, while getting data I need to take last 3 hours data only.

Following link not help me. because this link using specific date format which I don't have.
Getting data according to last hours from sqlite

Here Is my Query but seems it's not working.

SELECT * FROM Table1 where timestamp >= datetime('now','-3 hours')

Where timestamp is nothing but current system time while inserting.

Please give me any reference or hint.

Find the time millisecond of time that was 3 hours ago, then execute query to get the values with timestamp greater than this value. — Midhun MP, Dec 15 '15 at 07:55
if you want sqlite to give you a timestamp from the past, play a bit with `sqlite3` tool and execute the examples from [here](https://www.sqlite.org/lang_datefunc.html) and you will find that `SELECT strftime('%s','now');` is closest to what you want, the rest is your homework — pskink, Dec 15 '15 at 08:32
Consider the timezone as well or the results might not be accurate `SELECT * FROM Table1 where datetime(timestamp) >=datetime('now', '-3 Hour', 'localtime');` Please refer to https://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine — Eiad Samman, Feb 11 '21 at 10:39

score 9 · Accepted Answer · answered Dec 15 '15 at 08:05

9

You can get TimeStamp of Time 3 Hours back using following

Calendar cal = Calendar.getInstance();
cal.add(Calendar.HOUR, -3);
Date threeHourBack = cal.getTime();

Then you can pass threeHourBack.getTime() to the query.

Or You can do this in SQLite

SELECT * FROM Table1 where datetime(timestamp) >=datetime('now', '-3 Hour')

answered Dec 15 '15 at 08:05

Shashank Kumar

1,210
10
18

SELECT * FROM Table1 where datetime(timestamp) >=datetime('now', '-3 Hour') not working for me. I change it to SELECT * FROM Table1 where datetime(timestamp, 'unixepoch') >=datetime('now', '-3 Hour') – Wild Teddy Sep 13 '21 at 04:28
This will select including the current hour, so not good. – Mecanik Mar 04 '22 at 05:45

score 0 · Answer 2 · answered Dec 15 '15 at 07:59

0

there could be other methods. but I think this will work for you.

 SELECT * FROM Table1 where timestamp >= (System.currentTimeMillis()-108000000)

subtract 3 hours from current time and and search for time grater than that.

answered Dec 15 '15 at 07:59

Nabeel K

5,938
11
38
68

Abhishek Ginani · Answer 3 · 2015-12-15T08:50:11.180

0

Try this:

 SELECT * FROM Table1 where julianday(timestamp) >=  (julianday('now')-108000000)

edited Dec 15 '15 at 08:50

answered Dec 15 '15 at 08:01

Abhishek Ginani

4,511
4
23
35

score 0 · Answer 4 · answered Jul 01 '22 at 12:45

# https://www.sqlite.org/lang_datefunc.html
select DATETIME('now');                                  -- UTC time
select DATETIME('now', 'localtime');                     -- Local time
select DATETIME('now', 'localtime', '-5 hours');         -- Local time, but 5 hours ago
select DATETIME(0, 'unixepoch');                         -- 1970-01-01 00:00:00

select strftime('%s');                                   -- Current time in epoch UTC
select strftime('%s', '2022-07-01 00:00');               -- Converts to epoch UTC
select strftime('%s', '2022-07-01 00:00', 'localtime');  -- Converts to epoch

How to get Last 3 hours data from SQLite

4 Answers4

Linked