Date Closest to 'NOW'

Nagarajan asks:

I have needed a SQLite query that returns exactly a time which is very closest to the current time.

From 7:00pm,8:00pm, 9:00am and 10:00pm if the current time is 8:30pm, I will require 10:00pm.

This is more of a SQLite question than related to Cocoa Touch. But since SQLite is the mobile database engine of choice for Cocoa Touch, I’ll entertain this question as well. And follow it up with some Cocoa Code.

First some setup so that the demonstration has some data to demonstrate from:

CREATE TABLE dates (timestamp date);
INSERT INTO "dates" VALUES('2009-05-29 19:00:00');
INSERT INTO "dates" VALUES('2009-05-29 20:00:00');
INSERT INTO "dates" VALUES('2009-05-29 21:00:00');
INSERT INTO "dates" VALUES('2009-05-29 22:00:00');
SELECT * from "dates";

With this setup I can now query for the row which has the least distance to now. If you just subtract dates then you get the number of years. So I first convert it to juliandayss which are “number of days since noon in Greenwich on November 24, 4714 B.C”. Then I sort by distance from now descending and limit to just row.

SELECT timestamp
 FROM dates
 ORDER by (julianday(DATETIME('NOW')) - julianday(timestamp)) desc
 LIMIT 1;

What’s confusing for somebody new to SQLite is that you expect the row limiter to be called top like in T-SQL. I know, I did. But Google found me the SQLite documentation for SELECT and so I was able to figure this out.

Now there is one catch. From the question it appears that he is looking for FUTURE dates only. But alas that is possible too. Let’s pull the date arithmetic into it’s own column so that we can reuse. Subtracted from ‘Now’ all future dates have a negative distance.

SELECT timestamp, (julianday(DATETIME('NOW')) - julianday(timestamp)) AS date_dist
 FROM dates
 WHERE date_dist < 0
 ORDER BY date_dist desc
 LIMIT 1;

So far so good, having figured out the SQL the final step is to build this into a quick Cocoa app to demonstrate it. The need these prerequisites:

• Create a new project
• Add the SQLite framework/library with Add – Existing Frameworks (mine is located at /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS3.0.sdk/usr/lib/libsqlite3.dylib)
• Add the database to the project, it’s not enough for it to be in the project folder. Only if it’s added it will also be copied to the app bundle and be available in the app folder on the iPhone.
• Add the import for the SQLite headers #import <sqlite3.h>

// Opaque reference to the SQLite database.
sqlite3 *database;
NSString *path = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"test.sql"];
 
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
{
	const char *sql = "SELECT timestamp, (julianday(DATETIME('NOW')) - julianday(timestamp)) AS date_dist FROM dates WHERE date_dist < 0 ORDER BY date_dist desc LIMIT 1;";
 
	sqlite3_stmt *statement;
	// Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
	// The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.
	if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK)
	{
		// this could also be a while, but only need to read one row
		if (sqlite3_step(statement) == SQLITE_ROW)
		{
			// The second parameter indicates the column index into the result set.
 
			NSString *rfc2822String = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
 
			NSDateFormatter *dateFormatterToRead = [[NSDateFormatter alloc] init];
			[dateFormatterToRead setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; /* Unicode Locale Data Markup Language */
 
			NSDate *tmpTime = [dateFormatterToRead dateFromString:rfc2822String]; /*e.g. @"Thu, 11 Sep 2008 12:34:12" */
			NSLog(@"Time we retrieved: %@", tmpTime);
			[dateFormatterToRead release];
		}
 
		// "Finalize" the statement - releases the resources associated with the statement.
		sqlite3_finalize(statement);
	}
}

If you check the console output now after Build&Go you should see the correct date be display.

Finally there is one thing to be said about dates in SQLite versus Cocoa. You will notice that I am retrieving the timestamp as C-String and then use a date formatter to convert it into an NSDate. The reason is, that this framework does not have a different method of retrieving dates because SQLite is an open source C-Library and C does not have a date datatype. Objective-C has NSDate, but this is unknown to SQLite.

It’s because of this limitation that many people recommend saving dates or timestamps as double numbers instead. I personally prefer it as I’ve shown above because the performance tradeoff is negligible compared to the maintainability advantage that you get if you are able to go into your database, do a select and see the real date as opposed to a long number.

Categories: Q&A