How to sort sqlite database based on date?

Question

Possible Duplicate:
Sort the date in Sqlite database

I am creating an iPhone application that used SQLite database. In this I have a table that contains three columns and one column is "ReleaseDate" with DATE datatype. I want to sort the database with respect to the dates in this column. I used the following code. For inserting,

    NSDateFormatter *format = [[NSDateFormatter alloc]init];
    [format setDateFormat:@"MM/dd/yyyy"];
    NSDate *dateFromString = [[NSDate alloc] init];
    dateFromString = [format dateFromString:date];
    [format release];
    NSTimeInterval timeInterval = [dateFromString timeIntervalSince1970];

    sqlite3_bind_double(insertStmt, 4, timeInterval);

here, 'date' is a string value that holds the date value. Now, how can I retrieve data from entire columns from the DB? the query I used is,

select * from ItemTable  order by ReleaseDate desc

but sorting is not done.

FYI: if you were using Foundation types all the way down the stack, you could look into NSSortDescriptor. In general, it's a good way to sort arrays of structured information. — Eric G, May 25 '12 at 06:18

score 2 · Answer 1 · edited Apr 06 '14 at 14:10

2

There should be a format for dates in SQLite called DateTime. Try formatting your date to that as opposed to a String.

http://metacpan.org/pod/DateTime::Format::SQLite

edited Apr 06 '14 at 14:10

szabgab

6,202
11
50
64

answered May 24 '12 at 11:34

pandavenger

1,017
7
20

score 0 · Answer 2 · edited Nov 07 '18 at 12:30

0

Use like this: select * from ItemTable ORDER BY ReleaseDate ASC/DESC

edited Nov 07 '18 at 12:30

Deepzz

4,573
1
28
52

answered May 24 '12 at 11:38

score -1 · Answer 3 · edited May 29 '12 at 10:13

When you are inserting data use following code.

NSString *strTimeStamp = [NSString stringWithFormat:@"%lf",[[NSDate date] timeIntervalSince1970]];

Insert strTimeStamp to Database.

When you are getting data please use following function

NSTimeInterval timeInterval= [self intervalBeforeDays:2];

NSString *query = [NSString stringWithFormat:@"Select * from pictures where date >= %lf",timeInterval];



- (NSTimeInterval)intervalBeforeDays:(NSInteger)day{
    NSCalendar *gregorian = [[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar];
    NSDateComponents *dateComponents = [gregorian components:(NSDayCalendarUnit | NSMonthCalendarUnit | NSYearCalendarUnit) fromDate:[NSDate date]];
    NSInteger iday = [dateComponents day];
    NSInteger iEarlierDay = iday -day;
    dateComponents.day = iEarlierDay;
    NSDate *date = [gregorian dateFromComponents:dateComponents];
    NSTimeInterval interval = [date timeIntervalSince1970];
    [gregorian release];
    return interval;

}

How to sort sqlite database based on date?

3 Answers3