0

I am writing a query in OrmLite like:

                var items = db.Select<CustomTable>(query => query
                    .Where(record => record.UpdateTimestamp > lastUpdateTime)
                    .OrderBy(status => status.UpdateTimestamp)
                    .Limit(limit));
                return items;

This expression record.UpdateTimestamp > lastUpdateTime in the above query is returning true but the values of both of them are exactly the same in the database. Also on debugging the program, I find they store the exact same value which is: 2018-11-19 11:35:05.24345.

On further debugging, I found that the above query is working fine for timestamp which has the precision of time up to the 3rd decimal place. Eg:

Comparing 2018-11-19 11:35:05.123 and 2018-11-19 11:35:05.123 with > operator like in the above query returns false, but when I do increase the precision up to the 4th decimal place, then it starts failing.

Also, The UpdateTimestamp in CustomTable and lastUpdateTime both are a DateTime object only.

What could be the proper solution for this?

UPDATE I am getting the value of lastUpdateTime from the db, with the query like:

db.Select<SomeTable>(query => query.Where(row => "SomeKey" == key)).FirstOrDefault();
labilbe
  • 3,501
  • 2
  • 29
  • 34
Amit Upadhyay
  • 7,179
  • 4
  • 43
  • 57
  • `TimeStamp` is the wrong terminology, `DateTime` is more clear, as they are technically completely different things, in C# and SQL server – TheGeneral Nov 22 '18 at 04:31
  • It would be awesome if you could provide a [mcve] including how `lastUpdateTime` is populated. – mjwills Nov 22 '18 at 04:31
  • When you ran a DB trace, what was the **exact SQL** being submitted to the database? – mjwills Nov 22 '18 at 04:32
  • This one returns false `string s = "2018.11.19 11:35:05.24345"; DateTime d = DateTime.Parse(s,CultureInfo.InvariantCulture); DateTime d2 = DateTime.Parse(s, CultureInfo.InvariantCulture); bool res=d>d2;` – Access Denied Nov 22 '18 at 04:40
  • @mjwills I update question with the query I am using the fetch value of `lastUpdateTime` – Amit Upadhyay Nov 22 '18 at 04:40
  • 2
    @AmitUpadhyay mjwills wanted you to look at sql command submitted to sql server. – Access Denied Nov 22 '18 at 04:41
  • @AccessDenied I am still looking for a way to look at the last postgres command executed at my machine. Also, When I am executing the exact query on postgres console I get the fine results with the same timestamp. So it is highly possible that the ORM is truncating or rounding off in some way which is resulting this. – Amit Upadhyay Nov 22 '18 at 04:44
  • https://stackoverflow.com/a/16920626/34092 shows a way that may help. Or https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries . – mjwills Nov 22 '18 at 05:33
  • @mjwills In saw the log file which contains the query made, but for the queries made by the ORM they show `2018-11-22 09:44:40.615 IST [2300] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. ` They don't put the query hit by the ORM. However, the manual queries are being logged there. – Amit Upadhyay Nov 22 '18 at 06:38
  • Alas I can't help without knowing the SQL that was executed. – mjwills Nov 22 '18 at 21:41
  • @mjwills It looks as an ORM problem to me because if I execute the same query on Postgres console, results are fine then. It is likely that the ORM is truncating the value of `lastUpdatedTime` and then querying in the Postgres. What do you think, one should ideally do if the above case is happening? – Amit Upadhyay Nov 23 '18 at 09:37

0 Answers0