Getting last 12 hours worth of entries SQLite

Question

I'm trying to get the last 12 hours worth of entries from an SQLite db with android.

I was wondering if this was possible?

My date format is "yyyy-MM-dd HH:mm:ss.SSS" using simple date format.

I have a CREATED_AT column in my table which is of type TEXT.

I've got this far:

SELECT * FROM " + ENTRIES_TABLE + " WHERE " + CREATED_AT + ..."

Also, does it matter that the CREATED_AT column is TEXT rather than a datetime type?

Thanks

if your string matches then it does not matter if you are doing an `=` but your code does not explain this correctly. Also not a good idea though. What happens if you want to do `less than` or `between` — Scary Wombat, Apr 30 '14 at 00:34
You should look into storing `CREATED_AT` as a UNIX timestamp using sqlite `INTEGER`, instead of `STRING`. You'll read the value in your code using a long variable. This doesn't directly answer your question, but it would help with comparisons in the future. — ugo, Apr 30 '14 at 00:45
You should also look into accepting answers on your past questions. — ugo, Apr 30 '14 at 00:47

score 4 · Answer 1 · answered Apr 30 '14 at 07:42

SQLite uses dynamic typing; it does not matter if the column is declared as TEXT or DATETIME or FLUFFY BUNNIES.

SQLite's date format is designed so that string comparions work correctly. To subtract 12 hours from a timestamp, use one of the built-in date functions:

SELECT *
FROM Entries
WHERE CreatedAt >= datetime('now', '-12 hours')

(SQLite date functions use UTC by default; check that your table's values do not use some local timezone.)

score 0 · Answer 2 · answered Apr 30 '14 at 01:32

As a text field, you can't do a whole lot. In fact, your only option that works all the time without a great deal of pain is to parse the data and do it manually. The painful route would look at string comparisons directly, but that gets complicated if you are dealing with, say, Jan 1 before noon.

A much better way, and really the best way to deal with dates in sqlite, is to use a Unix timestamp. Then it would be a simple matter of selecting anything with a timetag greater than the current time minus 12 hours, and you're set.

Getting last 12 hours worth of entries SQLite

2 Answers2