-2

I'm working with Sqlite on Android. Within a table I got a column of String value that stores a timestamp in UTC formatted like this "yyyy-MM-dd HH:mm:ss". Now I want to compare that value against another timestamp that's in the same format. Let's say you want all recorders newer than "2013-01-01 00:00:00". But how to use this in a SELECT statement?

example:

  • Table: human
  • Column: (TEXT) name
  • Column: (TEXT) bornOnUtc

And now select all humans born after 2013-01-01 00:00:00?

Matthias
  • 5,574
  • 8
  • 61
  • 121
  • What about adding ` WHERE BornOnUTC > '2013-01-01 00:00:00'` to your rawQuery? – Phantômaxx Jun 27 '14 at 12:53
  • But that's just a string-comparison than and now really based on DateTime. – Matthias Jun 27 '14 at 12:58
  • And **how does it differ** from your accepted answer? Can you define DateTime, as interpreted by SQLite? It's really a string or an integer (depending on how you defined the field) in SQLite... – Phantômaxx Jun 27 '14 at 13:11
  • Because I did not know that you can actually compare date-time stamps based on strings. I later found [this post](http://stackoverflow.com/questions/13091000/sqlite-compare-dates), plaining why string-comparison works. So your answer was correct too, but I can not accept a comment as the correct answer. – Matthias Jun 27 '14 at 13:16
  • People should learn from posts on this website. Even though my question might have been very easy I still don't understand why people downvote it. It's not a stupid or silly one, just an easy one. – Matthias Jun 28 '14 at 13:44

1 Answers1

2

Try this:

SELECT *
FROM human
WHERE bornOnUtc >= '2013-01-01 00:00:00'
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
  • Thanks. Actually this is just string-comparison but [this post](http://stackoverflow.com/questions/13091000/sqlite-compare-dates) is a good explanation why this works. – Matthias Jun 27 '14 at 13:05
  • This works, too: SELECT * FROM human WHERE DATE(bornOnUtc) > DATE('2013-01-01 00:00:00'); – Matthias Jun 27 '14 at 13:09
  • 2
    @Matthias: Your method works but introduces **overhead** (i.e.: **worst performances**), because of the **2 conversions** – Phantômaxx Jun 27 '14 at 13:13
  • 1
    You are right. String comparison is the much better choice here. – Matthias Jun 27 '14 at 13:16