StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In SQLite, there is no date/time column type per se, so one ends up representing dates either as Julian date values (real columns) or in strings (text columns). SQLite is also very particular in how dates are represented in strings, yyyy-MM-dd HH:mm:ss (only).</p> <p>These are some methods that I wrote for working with SQLite dates from Objective-C. These methods are implemented in a category on NSDate.</p> <p>Be sure to check out the functionality that SQLite offers for working with Julian dates. I have found these to be quite useful (<a href="http://www.sqlite.org/lang_datefunc.html" rel="nofollow noreferrer">http://www.sqlite.org/lang_datefunc.html</a>). A function for deriving an NSDate's julianDay is included in the code example.</p> <p>It looks like this subject was also covered here. <a href="https://stackoverflow.com/questions/251155/persisting-dates-to-sqlite3-in-an-iphone-application">Persisting Dates to SQLite3 in an iPhone Application</a></p> <pre><code>+ (NSDate *) dateWithSQLiteRepresentation: (NSString *) myString; { NSAssert3(myString, @"%s: %d; %s; Invalid argument. myString == nil", __FILE__, __LINE__, __PRETTY_FUNCTION__); return [[self sqlLiteDateFormatter] dateFromString: myString]; } + (NSDate *) dateWithSQLiteRepresentation: (NSString *) myString timeZone: (NSString *) myTimeZone; { NSString * dateWithTimezone = nil; NSDate * result = nil; NSAssert3(myString, @"%s: %d; %s; Invalid argument. myString == nil", __FILE__, __LINE__, __PRETTY_FUNCTION__); NSAssert3(myTimeZone, @"%s: %d; %s; Invalid argument. myTimeZone == nil", __FILE__, __LINE__, __PRETTY_FUNCTION__); dateWithTimezone = [[NSString alloc] initWithFormat: @"%@ %@", myString, myTimeZone]; result = [[self sqlLiteDateFormatterWithTimezone] dateFromString: dateWithTimezone]; [dateWithTimezone release]; return result; } + (NSString *) sqlLiteDateFormat; { return @"yyyy-MM-dd HH:mm:ss"; } + (NSString *) sqlLiteDateFormatWithTimeZone; { static NSString * result = nil; if (!result) { result = [[NSString alloc] initWithFormat: @"%@ zzz", [self sqlLiteDateFormat]]; } return result; } + (NSDateFormatter *) sqlLiteDateFormatter; { static NSDateFormatter * _result = nil; if (!_result) { _result = [[NSDateFormatter alloc] init]; [_result setDateFormat: [self sqlLiteDateFormat]]; } return _result; } + (NSDateFormatter *) sqlLiteDateFormatterWithTimezone; { static NSDateFormatter * _result = nil; if (!_result) { _result = [[NSDateFormatter alloc] init]; [_result setDateFormat: [self sqlLiteDateFormatWithTimeZone]]; } return _result; } - (NSString *) sqlLiteDateRepresentation; { NSString * result = nil; result = [[NSDate sqlLiteDateFormatter] stringFromDate: self]; return result; } - (NSTimeInterval) unixTime; { NSTimeInterval result = [self timeIntervalSince1970]; return result; } #define SECONDS_PER_DAY 86400 #define JULIAN_DAY_OF_ZERO_UNIX_TIME 2440587.5 - (NSTimeInterval) julianDay; { return [self unixTime]/SECONDS_PER_DAY + JULIAN_DAY_OF_ZERO_UNIX_TIME; } + (NSDate *) dateWithJulianDay: (NSTimeInterval) myTimeInterval { NSDate *result = [self dateWithTimeIntervalSince1970: (myTimeInterval - JULIAN_DAY_OF_ZERO_UNIX_TIME) * SECONDS_PER_DAY]; return result; } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload