How can i get max data and minimum data from sqlite database in andorid?

Question

In my project i stored the dates in the dateadded filed like below.

01-07-14 12:00
02-07-14 12:00
25-06-14 13.00

When i fire query for minimum date:

Select dateadded from dgadata order by dateadded asc limit 1

That is return 01-07-14 12:00 and even if i write Select min(dateadded) from dgadata the output is same. But here the minimum date is 26-06-14 13.00

And in the max date i write like

Select dateadded from dgadata order by dateadded desc limit 1

Here the result is 25-06-14 13.00 but here the max date is 02-07-14 12:00

I think my query is fine i don't know why i am getting wrong result.

there is one option you can store you date in long and than short that data :) — MilapTank, Jul 04 '14 at 05:27

score 1 · Answer 1 · answered Jul 04 '14 at 06:45

SQLite doesn't have dedicated datetime types, but does have a few datetime functions. Follow the string representation formats (actually only formats 1-10) understood by those functions (storing the value as a string) and then you can use them, plus lexicographical comparison on the strings will match datetime comparison (as long as you don't try to compare dates to times or datetimes to times, which doesn't make a whole lot of sense anyway).

Depending on which language you use, you can even get automatic conversion. (Which doesn't apply to comparisons in SQL statements like the example, but will make your life easier.)

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

The reason is because the comparison is performed in the stored string. I think that this may help.

edited May 23 '17 at 11:49

Community

1
1

answered Jul 04 '14 at 05:18

Lefteris

873
2
8
29

score 0 · Answer 3 · answered Jul 04 '14 at 05:23

For what I can see, it seems that you have defined your dateadded field as TEXT, you should store a timestamp (integer) value instead of a text value, this way it will be easy to order them.

SQLite does not have a storage class set aside for storing dates and/or times. Instead,       
the built-in Date And Time Functions of SQLite are capable of storing dates and times    
as TEXT,     REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24,     
4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely 
convert between formats using the built-in date and time functions.`

Another possible solution is to store your dates as YYYMMMDDHHMMSS, this way your order by will work aswell.

How can i get max data and minimum data from sqlite database in andorid?

3 Answers3