Issue In Mysql Date Comparison

Question

I've a table that has a datetime column such like that :

 ---------------------
|   date_time_column  |
 ---------------------
| 2020-09-14 14:00:13 |
| 2020-05-18 14:00:13 |
| 2021-12-14 14:00:13 |
 ---------------------

when I made these query

select * from `table` 
        where TIMESTAMP('date_time_column') < '2020-12-14 10:20:04'

but the result is nothing

This is just because your client doesn't show the full resolution (some millisecond part) of the values. Hence none of them are exactly equal to the time you've requested. Try to get those between 10:20:04 and 10:20:05 instead. — Dojo, Dec 14 '20 at 09:08
If you use full datetime in where condition then no need to use TIMESTAMP, just use this select * from `table` where date_time_column < '2020-12-14 10:20:04'; — Shafiqul Islam, Dec 14 '20 at 09:09
'date_time_column' is a string. Perhaps you were thinking of \`date_time_column\` — Strawberry, Dec 14 '20 at 09:12
Have you tried debugging your query? What does `select *, timestamp('date_time_column') from table` yield? Also, can you share the schema for your table? — Nico Haase, Dec 14 '20 at 09:13
@NicoHaase I Just expect some rows that covers the condition I've given — Abdullah Al_Nahhal, Dec 14 '20 at 09:22
@ShafiqulIslam I've made these but there no result , although It gives me the correct result locally but doesn't give it on the cloud server, although I have the same DB and DB engine on both — Abdullah Al_Nahhal, Dec 14 '20 at 09:23

score 1 · Accepted Answer · answered Dec 14 '20 at 09:14

1

You are converting literal string 'date_time_column' to a timestamp, not the actual column. As this string obviously is not a valid timestamp, you get a null result, which filters out all rows.

select timestamp('date_time_column') as res

| res  |
| :--- |
| null |

You wanted backticks instead of single quotes, as in:

select * 
from `table` 
where timestamp(`date_time_column`) < '2020-12-14 10:20:04'

Not quoting the identifier is good enough too:

where timestamp(date_time_column) < '2020-12-14 10:20:04'

Bottom line, the timestamp conversion offers no benefit, and will slow down the query. Just do direct filtering:

where date_time_column < '2020-12-14 10:20:04'

answered Dec 14 '20 at 09:14

GMB

216,147
25
84
135

the problem is that I've made these query before and return no raw, when it works locally but doesn't work on server – Abdullah Al_Nahhal Dec 14 '20 at 09:20
@AbdullahAl_Nahhal: I don't know what you are saying. The answer explains the problem (the single quotes instead of backticks), and explains how to solve it. Did you try the suggested solution(s)? – GMB Dec 14 '20 at 09:28

Issue In Mysql Date Comparison

1 Answers1