Sqlite query, compare datetime field with current date time

Question

In my iPhone app i wanted to query past records. There is a datetime field which named as "task_dateTime" . If used, select task_dateTime from tasks; Results will look like This, Apr 26, 2012 16:32 Apr 27, 2012 16:38 Apr 29, 2012 05:32

How can i query past records. It means i wanted to compare with "task_dateTime" field with current time.

score 3 · Accepted Answer · edited Apr 27 '12 at 05:40

3

Try select task_dateTime from tasks where task_dataTime < CURRENT_TIME;

edited Apr 27 '12 at 05:40

Mithun Sreedharan

49,883
70
181
236

answered Apr 27 '12 at 05:21

gage

109
5

1

then try select task_dateTime from tasks where task_dataTime – gage Apr 27 '12 at 05:40
or select task_dateTime from tasks where julianday('now') - julianday(task_dateTime) > 0; – gage Apr 27 '12 at 05:47
I try with this one select task_dateTime from tasks where task_dateTime > 'Apr 26, 2012 15:22'; could you help me to format current date with "Apr 26, 2012 15:22" format – Susitha Apr 27 '12 at 05:51
1

CURRENT_DATE and CURRENT_TIME is keywords of sqlite. and datetime function know "now"is the current time. if you only want to get current time ,you needn't pass the time string from object-c to sqlite. If you mean you want to compare with the certain time like " Apr 26, 2012 16:32" , you must change the date format like "2012-4-26 16:32" and then use like task_dataTime < datetime("2012-4-26 16:32").you can convert the date with nsdateformatter. – gage Apr 27 '12 at 06:32
@gage this for hint it works for me NSString *sortSQL = [NSString stringWithFormat:@"delete from table_reminders where reminder_datetime < datetime('%@')",dateString]; – Swap-IOS-Android Dec 30 '14 at 08:51

score 1 · Answer 2 · edited Apr 27 '12 at 09:01

1

For current date : NSDateFormatter *formatter = [[NSDateFormatter alloc] init]; [formatter setDateFormat:@"MM-dd-yyyy"]; NSString *dateString=[formatter stringFromDate:[NSDate date]];

[current_date setText:dateString]; //current_date will be the name of your TextBox...

Then compare two strings...

by isequall to string

edited Apr 27 '12 at 09:01

answered Apr 27 '12 at 05:56

k.shree

119
1
2
12

Take array of past dates and compare it with present date.....DateFormate will be any thing you want ... – k.shree Apr 27 '12 at 05:58

Rob · Answer 3 · 2012-04-27T14:42:43.120

I'm assuming you're looking for SQLite date logic, where you can put the date logic in a where clause. If that's the case, it's worth noting that SQLite doesn't even have a proper date/time data type, as discussed here. Clearly you can use NSDateFormatter to do some conversions of your strings once you get it back to Objective C, but if you want date logic in your SQL where clauses, then you really have to conform to SQLite's guidance for the format of Time Strings outlined in SQLite Date Time Functions.

If you want a date in a human readable format, you can use something like "YYYY-MM-DD HH:MM:SS" format. See below for some examples. You can use NSDateFormatter to take this and convert it to a NSDate, from which you can then create date strings in whatever format you want for your UI.

Update:

Here is some sample SQL:

create table datetest (dt datetime);

insert into datetest values (datetime('now','-1 day'));
insert into datetest values (datetime('now'));
insert into datetest values (datetime('now','+1 day'));

select * from datetest;

select * from datetest where dt < datetime('now');

The first select statement will retrieve all three rows. The second returns just two of them.

Sqlite query, compare datetime field with current date time

3 Answers3

Linked