1

I am storing date fields as date field in Android Room. Of course it converts to timestamp for storing the date in sqlite database.

It looks like this:

enter image description here

I need to get the latest record of yesterday and today's last record (today's will be another query not in the same query).

As you would have seen in the screenshot, the first one is from yesterday and others are from today. So in this table I need two queries that gets the id: 1 and id: 3 records.

How can I achive that?

forpas
  • 160,666
  • 10
  • 38
  • 76
Tolgay Toklar
  • 4,151
  • 8
  • 43
  • 73

1 Answers1

1

You must use the function date() to convert the unix epoch timestamps to dates, so that you can filter the table for yesterday's and today's rows.
Then group by date and use SQLite's feature to return for each date the row with the max parseDate:

SELECT id, trackid, MAX(parseDate) parseDate
FROM tablename
WHERE date(parseDate / 1000, 'unixepoch') BETWEEN date(CURRENT_DATE, '-1 day') AND CURRENT_DATE
GROUP BY date(parseDate / 1000, 'unixepoch');

Or, if there are no rows with date later than today, you can simplify the WHERE clause:

WHERE date(parseDate / 1000, 'unixepoch') >= date(CURRENT_DATE, '-1 day')

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76