Sort sqlite database by time on android

Question

I am using this code-

myDB.rawQuery("SELECT * FROM " + TableName+ " ORDER BY "+ params[0], null);

to sort a table by columns and it works great except one of the columns is time (like in itunes) i.e. 2:35 for 2 minutes 35 seconds. This causes it to sort incorrectly. How can I sort it the proper way? I am using sqlite for android.

You table structure? sample data? – Yaqub Ahmad Jan 14 '12 at 04:45 — Yaqub Ahmad, Jan 14 '12 at 04:45

score 1 · Accepted Answer · answered Jan 14 '12 at 06:48

1

Instead of storing the date as 2:35, you can have the column store seconds, and would thus be 155 (2 minutes, 35 seconds = 155 seconds). Thus it would sort it fine. You could translate seconds to minutes/seconds in the code.

Or, if you wanted to, you could have two columns, minutes and seconds.

It does not make sense to store time in a format like 2:35 if you are manipulating the data.

answered Jan 14 '12 at 06:48

Dennis Sheil

729
5
16

Ah why didn't I think of that? I will store it in seconds thank you very much. – ghostbust555 Jan 14 '12 at 18:07

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

The best way is to store Time since epoch that is milliseconds since 01/01/1970 and you can then sort it however you like. Saving time like this lets you forget about worries of many issues that require effort.

Here it is an example.

edited May 23 '17 at 12:08

Community

1
1

answered Aug 09 '16 at 07:10

Muneeb Mirza

810
1
17
35

Sort sqlite database by time on android

2 Answers2