CODEWITHSUNDEEP
c#mysqlentity-framework
hot33331
hot33331

Reputation: 841

Compare String dates from mysql database with entity framework c#

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?

Upvotes: 1

Views: 1567

Answers (1)

DavidG
DavidG

Reputation: 118947

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;

Upvotes: 1

Related Questions