Saving Date to SQLite

Question

-(void)save:(id)sender
{
sqlite3_stmt *statement;
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &expenseDB) == SQLITE_OK)
{

    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO ADDEXPENSE(date, description, category, amount) VALUES (\"%@\", \"%@\", \"%@\",\"%@\")", fieldOne.text, fieldTwo.text,fieldThree.text , fieldFour.text];

    const char *insert_stmt = [insertSQL UTF8String];

    sqlite3_prepare_v2(expenseDB, insert_stmt, -1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE)
    {
      status.text = @"saved"
    } 
    else 
    {
       status.text = @"Failed"
    }
    sqlite3_finalize(statement);
    sqlite3_close(expenseDB);
}
}

This Method Works Fine For Me.But How to Save it as Date instead of strings? I tried a few ways but didnt work for me. As i am new to coding.someone please help me with a piece of code u u u u u u u u u This is the date Picker i used.

UIDatePicker *picker = (UIDatePicker *)sender;
NSDate *dateSelected = [picker date];
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateFormat:@"dd.MM.yyyy"];
self.fieldOne.text = [dateFormatter stringFromDate:dateSelected];

Have you considered consulting [the documentation](https://www.sqlite.org/datatype3.html#datetime)? — Hot Licks, Feb 03 '15 at 22:57

score 5 · Answer 1 · answered Nov 17 '11 at 10:06

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.

If you do this, I'd suggest explicitly setting ones date formatter to use `timeZone` of `[NSTimeZone timeZoneForSecondsFromGMT:0];` and a `locale` of `[NSLocale localeWithLocaleIdentifier:@"en_US_POSIX"]` as outlined in [Technical Q&A 1480](https://developer.apple.com/library/content/qa/qa1480/_index.html), which, while it's discussing "internet dates" applies equally well to storing dates in database. — Rob, Jan 23 '18 at 18:42

score 5 · Answer 2 · answered Nov 26 '11 at 09:20

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.

score 0 · Answer 3 · answered Nov 17 '11 at 09:58

0

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

answered Nov 17 '11 at 09:58

Vassan

56
1
4

Thats the site where i got this code from.there is nothing abt dates in that site. – Chandu Nov 17 '11 at 10:03

score 0 · Answer 4 · edited May 23 '17 at 11:46

0

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.

edited May 23 '17 at 11:46

Community

1
1

answered Nov 17 '11 at 10:10

alloc_iNit

5,173
2
26
54

score -1 · Answer 5 · edited Feb 03 '15 at 22:30

Code to add date to sqlite database table:

-(void) saveData



{



sqlite3_stmt *statement = NULL;

const char *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 database



    const char *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);

}
}

Saving Date to SQLite

5 Answers5

Linked