Saving Date To Sqlite

-(void)save:(id)sender { sqlite3_stmt *statement; const char *dbpath = [databasePath UTF8String]; if (sqlite3_open(dbpath, &expenseDB) == SQLITE_OK) { NSString *insertSQL

Solution 1:

SQLLite doesnt have a date datatype so you have to save it as either text or a number. I personally use the text method and my code converts any dates to text on insert or update and converts it back on a select.

so to get from a date I use

[self.dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm"];
NSString *dateString = [self.dateFormatter stringFromDate:date];

To convert it back

[self.dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm"];
NSDate *theDate = [self.dateFormatter dateFromString:ds];

I keep the value as a date until the point I want to display it.

Your code example needs some thought though as one of the key concepts in iOS development is MVC which means you should try and separate your View from your Model. Ideally you should have an object which has the data stored in it. Your View knows how to display the data and your Model knows how to store and retrieve the data.

It's worth looking into as it makes code more maintainable and extendible.

Solution 2:

It would be better to create a field of double data type in the DB and the save the date in timestamp using [NSDate timeinterversince1970]; this will make the NSDate to timestamp and you can save it in the database in double formate, further while use the similar type of function in NSDate class to convert the timestamp to NSDate.

Solution 3:

This best site to know Sqlite http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_4_iPhone_Application_%28Xcode_4%29

Solution 4:

Usually date value is being stored as string. But there are few ways that allows a developer to store a date value except date.

Following are the references those will let you know about the same.

1. Persisting Dates to SQLite3 in an iPhone Application

2. Objective-C and sqlite's DATETIME type

Solution 5:

Code to add date to sqlite database table:

---

-(void) saveData



{



sqlite3_stmt *statement = NULL;

constchar *dbpath = [databasePath UTF8String];
sqlite3_reset(statement);

if (sqlite3_open(dbpath, &recordsDB) == SQLITE_OK)

{



NSString* Date = [NSDate dateWithTimeIntervalSinceNow : sqlite3_column_double(statement, 0)];


    NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO RECORDS Date VALUES ( \"%@\")",Date];


    NSLog(@"save Data %@",Date);
    NSLog(@"save Data %@",insertSQL);  //to print the values inserted in the databaseconstchar *insert_stmt = [insertSQL UTF8String];

    sqlite3_prepare_v2(recordsDB, insert_stmt, -1, &statement, NULL);


    if (sqlite3_step(statement) == SQLITE_DONE)
    {

           NSLog(@"@database created");

        NSLog(@"databasevalue:%@",insertSQL);


    } else
    {
        NSLog(@"@database not created");
    }

    sqlite3_finalize(statement);


    sqlite3_close(recordsDB);

}
}