Objective-C and sqlite's DATETIME type
I have a sqlite3 table that I’m trying to map to an object in objective-C. One attribute of the table is ‘completed_at’ which is stored as a DATETIME.
I want to create a property on my objective-C class (which inherits from NSObject) that will map well to the ‘completed_at’ attribute.
Objective-C has an NSDate type but I’m not sure if that will map directly?
- Core Data setting One-To-Many in XCode 4.3.1
- Initialize Core Data With Default Data
- store images into sqlite database
- Swift 3 - UIButton adding setTitle from plist and database
- How to deal with accented characters in iOS SQLite?
- ios: using the same sqlite parameter more than once causes premature memory deallocation
4 Solutions Collect From Internet About “Objective-C and sqlite's DATETIME type”
I am sharing here just the core things regarding date formatting for saving and retrieving the data for presentation. If you have any problem with this code snippet then I will share the full code that I used for my project.
When you save your data, bind your date value in the sql statement like this way:
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init]; [dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSString *dateString=[dateFormat stringFromDate:[NSDate date]]; sqlite3_bind_text(saveStmt, 1, [dateString UTF8String] , -1, SQLITE_TRANSIENT);
and when you retrieve data you have to write this code:
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init]; [dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSDate *myDate =[dateFormat dateFromString:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)]];
now you have a variable myDate of NSDate type which you can render in your way:
NSDateFormatter *formatter = [[NSDateFormatter alloc] init]; [formatter setDateFormat:@"dd-MM-yyyy hh:mm:ss a"]; NSLog(@"My Date was : %@", [formatter stringFromDate:myDate]);
You must have to remember three things:
- In your SQLite date field type should be DATETIME
- Date format should be same when you store and when you retrieve
- Now you can show in your own way but following the format. Below the format details is given.
'dd' = Day 01-31 'MM' = Month 01-12 'yyyy' = Year 2000 'HH' = Hour in 24 hour 'hh' = Hour in 12 hour 'mm' = Minute 00-59 'ss' = Second 00-59 'a' = AM / PM
I have zero experience with Objective-C, but I found Apple’s NSDate Class Reference with a google search. With the information provided on the linked page you should be able to figure out how to manipulate 32-bit epoch times in Objective-C, and this would work well in SQLite. I would probably create the completed_at column as type INTEGER for 32-bit times.
SQLite really prefers Julian dates, which are floats. I haven’t found any documentation explaining how one might coerce the NSDate class into working with Julians.
timeIntervalSince1970 looks very interesting.
This came up a couple of weeks ago:
Persisting Dates to SQLite3 in an iPhone Application
The formatter is important if you are trying to effect the presentation but if you use if for internal storage, you are defining a string which can defeat the DB-engine’s ability to use the value for computation, comparison, sorting, etc. Also, if you are going to have different clients inserting the date value into the DB you would have to write conversion functions everywhere. I used the following and it worked as expected (schema’s column defined as DATETIME):
dateExpires = [NSDate dateWithTimeIntervalSinceNow: sqlite3_column_double(queryStmt, 5)];
I inserted into the SQLITE3 db with the Firefox add-on as “4/12/2010” here in Central time zone. Viewing the value of ‘dateExpires’ in XCode-debugger displayed as:
2010-04-12 23:19:48 -0500
Sure enough, that is the correct time.
Also, to insert into the SQLITE DB you will put the value
- Detect the reachability in background
- Remote web application debugging not working in ios 8 beta
- Which is the right one, nil or NULL, to mark “no Objective-C block”?
- Simplified screen capture: record video of only what appears within the layers of a UIView?
- UITextView text cut off when there is a large amount of text
- Will Objective-C runtime release retained associative references for user?
- String compare Objective C
- UITextField Only Top And Bottom Border
- iOS: Pulsing red double-height status bar
- iOS how to detect programmatically when top view controller is popped?
- Can I set the default simulator for new projects in Xcode 7.1?
- Swift mask of circle layer over UIView
- How to change RootViewController in AppDelegate From Other ViewController?
- Proper use of HKObserverQuery's background update completionHandler
- Beginner guide to OpenGLES on iPhone