Using SQLite3 on iPhone to get midnight (this morning) as a UNIX timestamp

Question

Okay, I really know this has GOT to be the long way around doing this... however, what I want is relatively simple one would think. I have a database with a timestamp column. I am using iPhone SDK with SQLite3. I want to have SQLite3 get all records for today (where timestamp >= midnight this morning) ..

What i have come up with (that works) is the following. BUT there has to be a way better way. This one takes far too many steps to be the best way to do it. (I want SQLite3 to figure the timestamp itself ... so please do not tell me to use the NSDate with NSCalendar, etc.)

select * from table where timestamp >= strftime('%s','now') - (strftime('%S',datetime('now','localtime')) + (strftime('%M',datetime('now','localtime')) * 60) + (strftime('%H',datetime('now','localtime')) * 3600));

There has to be an easier way (one, preferably, where i am not calling datetime and strftime 4 times or more each)

Answer 1

You just want records for today, or records for every day after today?

For today, just do:

select * from table where strftime('%Y-%m-%d', timestamp) = strftime('%Y-%m-%d', 'now')