1

I have a database field like this:

Timestamp varchar(19) utf8mb4_unicode_ci

containing a timestamp (string) like this

"2013-05-29 00:00:00"

I am using the entity framework and I would like to filter by time - meaning I would like to get all entries having a timestamp > (now-interval). Code would look something like this

var query = from r in db.route
                    where
                        r.timestamp > (now-interval);
                    select r;

How can I do this?

hot33331
  • 805
  • 11
  • 25
  • 2
    Why are your dates stored as varchar in your database? Can you change that? – DavidG Oct 13 '15 at 12:17
  • 2
    Possible duplicate of [How do I perform Date Comparison in EF query?](http://stackoverflow.com/questions/1088212/how-do-i-perform-date-comparison-in-ef-query) – gpinkas Oct 13 '15 at 12:18
  • @gpinkas Not a dup since that deals with a DATE column not a VARCHAR. – juharr Oct 13 '15 at 12:27
  • 2
    You should absolutely change the DB design if possible. Otherwise you're left with retrieving all the records and doing the parsing and filtering in memory, which will be much slower. – juharr Oct 13 '15 at 12:28
  • @juharr Not entirely true as the "date" format is in a usable format for ordering alphanumerically. – DavidG Oct 13 '15 at 12:45

1 Answers1

1

My first suggestion would be to fix the database so the date values are stored as the correct date type. That would solve many issues as well as increase search performance. However, in the (unlikely) situation that you are unable to do that, and that the format of the values in that column all match exactly as you specified in the question, you could convert your local time stamp variable to a string and compare it directly. As the format you have shown has an alphanumeric ordering that is identical to the date order, it should work:

//It's important that this is done here and not inline with the Linq query
var nowInterval = timeStamp.ToString("yyyy-MM-dd HH:mm:ss");

var query = from r in db.route
            where string.Compare(r.timestamp, nowInterval, StringComparison.Ordinal) > 0
            select r;
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • Shouldn't you use `HH` for 24 hour clock? – juharr Oct 13 '15 at 13:16
  • @juharr Oops, well spotted! – DavidG Oct 13 '15 at 13:17
  • Thanks for all the advice! Unfortunately the DB is not at my liberty to change :( When I use your code visual studio reminds me that it cannot apply operator '>' on string and string. It seems that something like: `SELECT * FROM yourTable WHERE STR_TO_DATE(my_column_with_the_string_date, '%d.%m.%Y') <= '2011-09-30'` could solve the problem, I just don't know how to put it in "Entity FW writing". Any ideas? – hot33331 Oct 14 '15 at 07:51
  • @hot33331 Ah yes, I forgot about that. Instead of using `>` you can use `string.Compare`, so in this case it will be something like this: `string.Compare(r.timestamp, nowInterval, StringComparison.Ordinal) > 0` – DavidG Oct 14 '15 at 08:17