Compare String dates from mysql database with entity framework c#

Question

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?

Why are your dates stored as varchar in your database? Can you change that? — DavidG
– DavidG, Commented Oct 13, 2015 at 12:17
Possible duplicate of How do I perform Date Comparison in EF query? — gpinkas
– gpinkas, Commented Oct 13, 2015 at 12:18
@gpinkas Not a dup since that deals with a DATE column not a VARCHAR. — juharr
– juharr, Commented Oct 13, 2015 at 12:27
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
– juharr, Commented Oct 13, 2015 at 12:28
@juharr Not entirely true as the "date" format is in a usable format for ordering alphanumerically. — DavidG
– DavidG, Commented Oct 13, 2015 at 12:45

DavidG · Accepted Answer · 2015-10-14 09:14:50Z

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;

edited Oct 14, 2015 at 9:14

answered Oct 13, 2015 at 12:44

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

juharr Over a year ago

Shouldn't you use HH for 24 hour clock?

DavidG Over a year ago

@juharr Oops, well spotted!

hot33331 Over a year ago

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?

DavidG Over a year ago

@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

Collectives™ on Stack Overflow

Compare String dates from mysql database with entity framework c#

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related