CODEWITHSUNDEEP
pythonmysqlintervals
Wei Zhang
Wei Zhang

Reputation: 11

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.

Upvotes: 1

Views: 997

Answers (2)

Adam Morris
Adam Morris

Reputation: 8545

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.

Upvotes: 2

Mchl
Mchl

Reputation: 62387

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.

Upvotes: 2

Related Questions