Search code examples
iosobjective-csqlitedatetimefmdb

SQLite Datetime before 24 hours query

I have a database table and storing a datetime column with the UTC datetime, iOS [NSDate date] is returning always a UTC datetime. It's all good about it.

I'm using FMDB to get the last 12 and 24 hours data with the following code.

[self.fmQueue inDatabase:^(FMDatabase *db)
   {
   NSString* queryCommand = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ >= Datetime('?')", readingsTable, dateTimeRecordedColumn];
   NSDate* dateBefore24Hours = [self.dateUtilities dateBySubtractingDays:1 forDate:[NSDate date]];
   WEELogDebug(@"Date before 24 hours: %@", dateBefore24Hours);
   FMResultSet* result = [db executeQuery:queryCommand, dateBefore24Hours];
   if (completionblock)
      {
      completionblock([self readingsArrayFromResultSet:result]);
      }
   }];

What I need to accomplish is retrieve all data that has a date after the dateBefore24Hours variable. The debug message shows the correct value, for example [NSDate date] now before 24 hours is, Date before 24 hours: 2015-04-06 15:28:08 +0000, so I should get al the records until 2015-04-07 15:28:08 +0000 with the above query, but I don't.

With SQLite Studio I provide you with the records I have at the moment in the database.

Records in SQLite

Solution

  • You do not need the Datetime() function. (And even if you did, one would never put the ? within quotation marks.)

    When FMDB encounters a NSDate object in the list of parameters, it will automatically do timeZoneForSecondsFromGMT (if you didn't specify a dateFormat string), and so this numeric representation of the date can be used when comparing to your dateTimeRecorded column. By the way, this numeric value can be accessed in SQLite as a unixepoch value.

    Bottom line, you should simply be able to use ?:

    SELECT * FROM %@ WHERE %@ >= ?