Mysql INTERVAL sql statement problem
I am using the the following sql in MySQL terminal and it works fine.
select * from metric_measured_value where metric_id=18 and measurement_time>(now()-INTERVAL 2 year);
However, when I used it in python code, it won't work.
res = self._db.Query("""SELECT * FROM metric_measured_value
WHERE metric_id = %s
AND measurement_time>(now()-%s)""",
(metric_id, max_interval))
Here metric_开发者_开发问答id=18 and max_interval="INTERVAL 2 year"
The error message is: Truncated incorrect DOUBLE value: 'INTERVAL 2 year'
What am I doing wrong?
Thanks for you help.
This is probably your DB access library utilizes prepared statements. INTERVAL
is a MySQL keyword and it is not expected to be passed into prepared statements.
Instead of calculating the interval on the database, why not do it in python, and pass it into your measurement time.
from datetime import date
max_interval = 2
today = date.today()
interval = date.today()
interval = interval.replace(year=interval.year - max_interval)
delta = today - interval
res = self._db.Query("""SELECT * FROM metric_measured_value
WHERE metric_id = %s
AND measurement_time > %s""",
(metric_id, delta.days))
Just saw this question on gmail style date formating, which suggested a relative dates module. It's more relative to now, but it might give some ideas.
精彩评论