0

i am creating app with sqlite database, i have table event

CREATE TABLE "Events" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "Date" DATETIME, "Title" TEXT, "ImgUrl" TEXT)

where i have taken Date as DATETIME, i want table data sorted on date , but it is not giving me sorted data,

I have tried in SQLITE MANAGER but here also it is not shows properly sorted data.

enter image description here

EDIT Sorting in Sqlite manager click on Date column. still not getting sorted data. enter image description here

enter image description here

  • @Reformer: Why downvoting.?? please explain. –  Jan 20 '14 at 05:49
  • @Reformer: i know how to use it with sqlite mgr. firfox plugin,when i click on date it also not give sorted date., check updated question –  Jan 20 '14 at 06:08

3 Answers3

1

It looks like your query is sorting with your Date column lexicographically as though the type is text. You might prefer an integral type. Your options include:

1.2 Date and Time Datatype

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.

See here.

Ken
  • 30,811
  • 34
  • 116
  • 155
  • so why it is show DATETIME type when i am creating table if it doesnt support,? –  Jan 20 '14 at 06:14
  • Thanks , i have updated table and store date as **'yyyy-mm-dd'** format. –  Jan 20 '14 at 06:24
0

SQLite doesn't have DATETime type, the result is because it's using varchar sort.

http://www.sqlite.org/datatype3.html

this is a workable solution https://stackoverflow.com/a/7161459/94962

Community
  • 1
  • 1
limboy
  • 3,879
  • 7
  • 37
  • 53
  • Thnks for replay, but when creating table it shows column type `DATETIME`. –  Jan 20 '14 at 06:09
0

Here you got a sorted result but it's by day of the month does not date as you expected.The SQLite sort date in the format of 'YYYY-mm-dd', if you have the date which is not in the form of 'YYYY-mm-dd', convert to this format. Modify your query as

SELECT * FROM Events ORDER BY (substr(Date, 10, 7) || '-' || substr(Date, 5, 4) || '-' || substr(Date, 1, 2));

Hope this will help you.

Rahul Panzade
  • 1,302
  • 15
  • 12