开发者

Timestamp & Date comparison in MySQL - ambiguous error

this is a follow up to a question I got help with on here yesterday, but it's a slightly different issue - i'm trying to check if a mysql timestam开发者_JAVA百科p of a db record matches 7 days in the past (ignoring hours and seconds etc), but I'm getting a "Column 'import_date' in where clause is ambiguous", can anyone see the problem?

This is the date comparison: Timestamp, e.g 2010-07-13 11:04:27

WHERE date_format( `import_date`, "%Y-%m-%d" ) =
    date_format( date_sub( now(), INTERVAL 11 DAY ), "%Y-%m-%d" ) 


Regarding your ambiguous error you must have 2 tables in the query both with an import_date column. You would need to use

WHERE date_format( `YourTableName`.`import_date`, "%Y-%m-%d" ) =
    date_format( date_sub( now(), INTERVAL 11 DAY ), "%Y-%m-%d" ) 

Just to add though that your query above will be inefficient as it is not SARGABLE

I'm not familiar enough with MySQL to know exactly what it's doing but definitely you will be better off rewriting it as

WHERE `YourTableName`.`import_date` = x

or

WHERE `YourTableName`.`import_date` between x and y

as appropriate

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜