SQLite storing, retrieving and comparing a DATETIME field

Question

I am really stuck trying to compare dates in SQLite queries in Objective C. Here's what I'm doing:

Storing the date:

This document tells me to use the dateformat specified below, but it doesn't seem right. I tried using yyyy-MM-dd hh:mm:ss without success too though.

NSDate *today = [NSDate date];
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"YYYY-MM-DD HH:MM:SS"];
NSString *dateString=[dateFormat stringFromDate:today];

NSString *query = [NSString stringWithFormat: @"INSERT INTO user (edited) VALUES (\"%@\")", dateString];

Comparing the date

I am using a timestamp for this, which I convert to a datetime string

long stamp = [sessie integerForKey:@"stamp"];

NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
[formatter setDateFormat:@"YYYY-MM-DD HH:MM:SS"];
NSString *dateString = [formatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:stamp]];

sqlite3_stmt *result = [db selectQuery:[NSString stringWithFormat:@"SELECT * FROM user WHERE edited > '%@'", dateString]];

The timestamp is simply generated using [[NSDate date] timeIntervalSince1970]. The problem is that the query won't give the correct results, and I don't even know if the date is stored correctly in the first place. Any help would be greatly appreciated!

Answer 1

A couple of observations:

1. For your date string, you do definitely do not want to use YYYY-MM-DD HH:MM:SS. That will not generate a valid date string. Using yyyy-MM-dd hh:mm:ss is much closer, but not quite right, either (since you'll use 12-hour hh). Use yyyy-MM-dd HH:mm:ss instead.

2. This date format, though, does not capture time zone, so, if you store date strings in your SQLite database, you should use UTC (GMT) as discussed in the SQLite Date And Time Functions documentation.

   NSDateFormatter * formatter = [[NSDateFormatter alloc] init];
   [formatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
   formatter.timeZone = [NSTimeZone timeZoneWithName:@"UTC"];
   formatter.locale = [[NSLocale alloc] initWithLocaleIdentifier:@"en_US_POSIX"];
   NSString *dateString = [formatter stringFromDate:today];
   

   As shown above, you probably want to also specify the locale so that the format of the dates will not change depending upon the localization settings of the device.

3. Note that you might consider using timeIntervalSince1970 to store the date as a REAL value in your database (as opposed to a TEXT). This can be a little more efficient and automatically addresses the UTC issue.