Work with databases in iPhone, SQLite and work with dates

Work with databases in iPhone, SQLite and work with dates

It is known that SQLite in the struggle for lightness and compactness does not directly support the “Dates” format. Having met this problem when creating an iPhone application (and SQLite is the only DBMS for the iPhone), I did not find any worthy documentation in Russian. What caused the desire to write this post.

To address this issue, I turned to www.sqlite.org/lang_datefunc.html

In my case, I decided to keep the date in text format (ie, the TEXT data type):
')
CREATE TABLE regDoc ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'nameDoc' TEXT, 'dateDoc' TEXT)

When working with such an approach, it is important to observe the format of data presentation (dates) throughout the entire application.

SQLite supports the following date functions:

Function (Equal -) Equivalent strftime ()
date (...) - strftime ('% Y-% m-% d', ...)
time (...) - strftime ('% H:% M:% S', ...)
datetime (...) - strftime ('% Y-% m-% d% H:% M:% S', ...)
julianday (...) - strftime ('% J', ...)

Using basic functions has difficulty with date formats for non-European and US countries, for example, in Russia, the date has the format% d.% M.% Y, so I use the analogue of these functions strftime ().

Example of working with the date in SQLite

const char * sql = "select id, nameDoc, dateDoc from regDoc where (strftime ('% d.% m.% Y', dateDoc) <= strftime ('% d.% m.% Y',?)) and (strftime ('% d.% m.% Y', dateDoc)> = strftime ('% d.% m.% Y' ,?)) order by strftime ('% d.% m.% Y', dateDoc ) DESC;


Here we see the conditions of selection by date and sorting. The date in the table is stored as text.
Analyze the condition

(strftime ('% d.% m.% Y', dateDoc) <= strftime ('% d.% m.% Y',?))

What does it mean - If dateDoc is less than or equal to the entered date

An example of the full function of SQLite and Objective-C (Xcode)
-(NSMutableArray *)selectListDocFrom:(NSString *)dateOn:(NSString *)dateFrom:(int)flagInOut{
// ( // )
if ([self initConnect]) {
sqlite3_stmt *statement;
// — ? (
//
const char *sql = "select id,nameDoc,dateDoc from regDoc where (strftime('%d.%m.%Y',dateDoc)<=strftime('%d.%m.%Y',?)) and(strftime('%d.%m.%Y',dateDoc)>=strftime('%d.%m.%Y',?)) order by strftime('%d.%m.%Y',dateDoc) DESC";

//
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
//
// sqlite3_bind_text
// !
// 1 0
sqlite3_bind_text(statement, 1, [dateOn UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 2, [dateFrom UTF8String], -1, SQLITE_TRANSIENT);
// —
// UTF8String
if (flagInOut==0)
{
sqlite3_bind_text(statement, 3, [@"+" UTF8String], -1, SQLITE_TRANSIENT);
} else if (flagInOut==1)
{
sqlite3_bind_text(statement, 3, [@"-" UTF8String], -1, SQLITE_TRANSIENT);
}
//
while (sqlite3_step(statement) == SQLITE_ROW) {
//
//
//sqlite3_column_text sqlite3_column_int sqlite3_column_float
//
// ( ( )) 0 1

[self.records addObject:[NSDictionary dictionaryWithObjectsAndKeys:[NSString stringWithFormat:@"%d",sqlite3_column_int(statement, 0)],@"id", [NSString stringWithUTF8String:(char *) sqlite3_column_text(statement, 1)], @"dateDoc",[NSString stringWithFormat:@"%00.00f",sqlite3_column_double(statement, 2)],@"summa",[NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 3)],@"typeOperation",nil]];
}
//
//
return self.records;

} else
//
{NSAssert1(NO, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));};
} else {
//

NSAssert1(NO, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
}
return nil;

}