sqlite3 datetime not functioning properly on iPhone

Question

Hello,

I am having trouble getting sqlite3 to do comparisons between datetimes. On my mac, when I run the sql queries, it works. However, on the iPhone, the exact same query fails.

I have a sqlite3 table setup as follows:

CREATE TABLE IF NOT EXISTS `Artists` (
`id` int(11) NOT NULL PRIMARY KEY, 
`name` varchar(256) NOT NULL,
`lastUpdate` date NOT NULL default CURRENT_TIMESTAMP ,
...
...
);

I can insert an artist with:

INSERT OR IGNORE INTO `Artists` (`id`,`name`) VALUES ('1','Justin');

I am trying to find the number of artists that have not been updated for the last 2 seconds (to keep it short):

SELECT COUNT(*) FROM `Artists` WHERE `id` = ? AND `lastUpdate` < datetime('now','-2 seconds');

On the iPhone, this returns 0. On my pc, it returns 1. I am expecting the value to be 1. What is the difference? I am retrieving the value by:

-(BOOL) artistNeedsUpdating:(int)artistId
{
    [dbLock lock];
    BOOL result = NO;
    sqlite3_stmt *statement;

    const char* query = "SELECT COUNT(*) FROM `Artists` WHERE `id` = ? AND `lastUpdate` < datetime('now','-2 seconds');";
    if(sqlite3_prepare_v2(database, query, -1, &statement, NULL) == SQLITE_OK){
        sqlite3_bind_int(statement, 1, artistId);

        if(sqlite3_step(statement) == SQLITE_ROW){
            if(sqlite3_column_int(statement, 0) > 0) // If 0, then we don't need to update
                result = YES;
        }

        sqlite3_finalize(statement);
    }
    [dbLock unlock];
    return result;
}

I am confused as to why it works on one platform but not the other, does anybody have any ideas?

Thank you, Justin

Answer 1

Nevermind, it was my mistake.

I was updating the row after it got done with

UPDATE `Artists` SET `lastUpdate`='CURRENT_TIMESTAMP';

When it should have been:

UPDATE `Artists` SET `lastUpdate`=CURRENT_TIMESTAMP;