Fetching data from sqlite database based on timestamp of present day

Question

In my android sqlite database there is a column that contains the timestamp in the format (2013-12-12 18:47:31) of the data. Now a particular data may have time stamp which is earlier than present day or later than present day. e.g. today its 20 December 2013, and database contains data of 1 November 2013, 6 November 2013, 12 December 2013, 1 January 2014 etc... Now I want to fetch data which are earlier than present day i.e. 20 December 2013. How to do that?

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

select [Columns] from [database] where [column_date] < "currentDate";

To get current date and time, check this link Get current time and date on Android

edited May 23 '17 at 11:56

Community

1
1

answered Dec 20 '13 at 11:42

Anatol

941
1
7
13

score 2 · Answer 2 · edited May 23 '17 at 10:25

you can use the following query to fetch the data based on timestamp

SELECT [COLUMN 1], [COLUMN 2]...., [COLUMN N] FROM [TABLE] WHERE TIMESTAMP_FIELD < strftime('%s','now');

- Date And Time Functions

Else you can also fetch currentTimeStamp like this

SELECT [COLUMN 1], [COLUMN 2]...., [COLUMN N] FROM [TABLE] WHERE TIMESTAMP_FIELD <
CAST((((JulianDay('now', 'localtime') - 2440587.5)*86400.0) + 62135596800) * 10000000
AS BIGINT)

- Link for reference

score 1 · Answer 3 · answered Dec 20 '13 at 11:40

1

yyyy-MM-dd HH:mm:ss datetime stamps have the same lexicographical and chronological order. You can use operators such as < and > on the strings.

answered Dec 20 '13 at 11:40

laalto

150,114
66
286
303

score 0 · Answer 4 · edited Sep 21 '17 at 06:44

0

Time time = new Time();

time.setToNow();

OR

Date todayDate = new Date();

todayDate.getDay();

todayDate.getHours();

todayDate.getMinutes();

todayDate.getMonth();

todayDate.getTime();

edited Sep 21 '17 at 06:44

Ravindra Kushwaha

7,846
14
53
103

answered Dec 20 '13 at 11:51

Ishan_Deb

11
1
3

score 0 · Answer 5 · answered May 31 '19 at 14:49

I had this problem of fetching data using timestamps but to no avail after weeks i got a good solution.

set column timestamp as text on your table

 public static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + COLUMN_INCOME + " INTEGER,"
                    + COLUMN_EXPENSE + " INTEGER,"
                    + COLUMN_TIMESTAMP + " TEXT"
                    + ")";

get the date using new Date() and convert to string

 private String getDateTime() {
        DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }

Insert data in your table using the getDateTime() for the timestamp

ContentValues values = new ContentValues();

        values.put(DailyTransaction.COLUMN_INCOME, income);
        values.put(DailyTransaction.COLUMN_EXPENSE, expense);
        values.put(DailyTransaction.COLUMN_TIMESTAMP, getDateTime());

Then your sqlite query will look like this:

"SELECT * FROM "+ TABLE_NAME + " WHERE " + ClassName.COLUMN_TIMESTAMP + " <"+ "'"currentDate'";

Fetching data from sqlite database based on timestamp of present day

5 Answers5