Mysql INTERVAL sql statement problem

Question

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.

Mchl · Accepted Answer · 2011-08-01 07:49:25Z

2

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.

answered Aug 1, 2011 at 7:49

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wei Zhang Over a year ago

Thank you. That's the problem. Is there a way to avoid this?

Mchl Over a year ago

What Adam suggests is probably the best way.

Community · Accepted Answer · 2017-05-23 12:04:17Z

2

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.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Aug 1, 2011 at 8:41

Adam Morris

8,57513 gold badges47 silver badges68 bronze badges

3 Comments

Wei Zhang Over a year ago

Thanks for the answer. It's a good idea, but here the interval is not necessarily year, it might be minute, hour, day, month, week or year. Is there a way to easily do this?

Adam Morris Over a year ago

If you know the type, you can write a switch statement. Then, use datetime.date to calculate difference in days, weeks, months, years, and datetime.datetime to calculate the difference in seconds, minutes and hours.

Wei Zhang Over a year ago

Yes, I guess that's the best option I should try. Thanks Adam.

Collectives™ on Stack Overflow

Mysql INTERVAL sql statement problem

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related