11

Below is the snapshot of what I have got as a query from sqlite db.

After googling and reading a number of question around, I have come to know that to retrieve maximum datetime using aggregate functions like max() is not possible as sqlite doesn't support much datatypes but treats datatype as text.

So, I have brought this data in a List or at java level. So how could I now get the maximum datetime from this list.

Is there any direct construct for this format in java. Or do we have something at sqlite level that I coudn't find.

Sqlite returned data

Prateek
  • 3,923
  • 6
  • 41
  • 79
  • If you use an auto incrementing unique id, then the highest unique id should theoretically get you the most recent date time. – EGHDK Dec 12 '12 at 10:53
  • basically I want `current_test_id` where `test_datetime` is maximum. – Prateek Dec 12 '12 at 10:53
  • use max and sort dates as long (however, your format would work too, as it seems comparable) – njzk2 Dec 12 '12 at 10:55
  • I know this isn't the answer you were looking for, but then wouldn't retrieving the latest entry give you the latest `test_datetime`? – EGHDK Dec 12 '12 at 10:56
  • @njzk2 can he sort dates as long since the date is formatted? ie: 2012-10-19 06:45:40 – EGHDK Dec 12 '12 at 10:57
  • store your date as LONG in db (EPOCH time) to retrive and show such date you can use code from http://stackoverflow.com/questions/10139141/sort-db-data-and-display-in-list-view#10140140 – Selvin Dec 12 '12 at 10:58
  • @EGHDK : How to I get maximum or highest id in sqlite . – Prateek Dec 12 '12 at 10:59
  • @Selvin can't change schema now! – Prateek Dec 12 '12 at 11:00
  • @pKs not sure... heading off to bed now. Maybe this? http://stackoverflow.com/questions/7575166/android-sqlite-get-last-insert-row-id – EGHDK Dec 12 '12 at 11:05

4 Answers4

19

texts can be compared, sorted and ordered in SQLite.

Your format appears to be YYYY-MM-dd hh:mm:ss. Lucky for you, ordering this format result in ordering by date.

just

select current_test_id from someTable order by test_datetime desc limit 1

or

select current_test_id, max(test_datetime) from someTable

(or something, not entirely sure for the second one)

njzk2
  • 38,969
  • 7
  • 69
  • 107
  • awesome man first one worked. But if the format of date-time were something different. Event in that case would it work fine? – Prateek Dec 12 '12 at 11:12
  • 1
    as long as your date format can be sorted, that will work. The key part here is that the strings have the same length, and the values in the string are sorted by significance (year is more significant than month and so on.) If you invert any 2, it will no longer work. Next time i strongly recommend using longs for date storage. – njzk2 Dec 12 '12 at 12:54
  • If you are using room do @Query("select current_test_id, max(test_datetime) as test_datetime from someTable") fun getLast(): Single because it not going to find the "max(test_datetime)" column :S – moralejaSinCuentoNiProverbio Feb 15 '20 at 20:17
2

if you set the type of datetime field text then you can perform following query but datetime must be yyyy-mm-dd hh:mm:ss

select max(datetime) from tableName;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
USKMobility
  • 5,721
  • 2
  • 27
  • 34
0

A common approach is to store the data converted as long.

Use date.getTime() to get long from your Date instance and Date date = new Date(timestamp); to get a date object from your timestamp.

Once you have a long in your db you can perform any ordering / comparison you want.

fedepaol
  • 6,834
  • 3
  • 27
  • 34
0

To retrive max value from a set of Time of type (String) We have to do some concatenations using sub string .Using this Query max or min Time can be find out using sql lite 

select max(datetime(case 
when substr(TimeIn,7,2)='PM' 
then substr(TimeIn,1,2)+12 
else substr(TimeIn,1,2) 
end  || ':' || substr(TimeIn,4,2) || ':' || '00'))  
from tablename 
where Date='10/06/2016'
Samjin Mahadevan
  • 47
  • 5