Interval MySQL

[ricardoorfao]

Pypika messes up Interval dates when using MySQL queries:

from pypika import functions as fn

fruits = Tables('fruits')
q = MySQLQuery.from_(fruits) \
    .select(fruits.id, fruits.name) \
    .where(fruits.harvest_date + Interval(months=1) < fn.Now())
str(q)

SELECT `fruits`.`id`,`fruits`.`name` FROM `fruits` JOIN `consumers` ON `fruits`.`consumer_id`=`consumers`.`id` WHERE `fruits`.`date`>=NOW()-INTERVAL `1 DAY`

I believe that the problem is in line 1080 of terms.py:

return self.templates.get(dialect, 'INTERVAL \'{expr} {unit}\'')

[twheys]

Please use the MySQLQuery class to create your query if you want MySQL specific syntax. See https://pypika.readthedocs.io/en/latest/3_advanced.html#handling-different-database-platforms

[ricardoorfao]

The problem is precisely that, I'm already using MySQLQuery. I've updated the issue.

[twheys]

Ah, upon rereading the original issue, I think I see what your issue is. This line .where(fruits.harvest_date + Interval(months=1) < fn.Now()).

I think you need to wrap the addition with brackets in order to get the correct order of operations. Can you try .where((fruits.harvest_date + Interval(months=1)) < fn.Now())?

[twheys]

If that isn't right, please supply me the test code, the actual output, and the expected output, please. That way I can troubleshoot it.

[ricardoorfao]

from pypika import functions as fn
from pypika import Tables, MySQLQuery, Interval

fruits, consumers = Tables('fruits', 'consumers')
q = MySQLQuery.from_(fruits) \
    .join(consumers) \
    .on(fruits.consumer_id == consumers.id) \
    .select(fruits.id, fruits.name) \
    .where((fruits.harvest_date + Interval(days=1)) < fn.Now())
print(q.get_sql())

Result:

SELECT `fruits`.`id`,`fruits`.`name` FROM `fruits` JOIN `consumers` ON `fruits`.`consumer_id`=`consumers`.`id` WHERE `fruits`.`harvest_date`+INTERVAL '1 DAY'<NOW()

Expected result:

SELECT `fruits`.`id`,`fruits`.`name` FROM `fruits` JOIN `consumers` ON `fruits`.`consumer_id`=`consumers`.`id` WHERE `fruits`.`harvest_date`+INTERVAL 1 DAY<NOW()

I also noticed that the desired result is showed if str(q) is used instead of q.get_sql().

[Robbie-Palmer]

I'm having the same issue with using Interval only using str(q) doesn't solve my problem as I'm generating SQL for GCP BigQuery which requires me to remove all quote chars with q.get_sql(quote_char=None) From Interval(quarters=1) I'm looking to generate INTERVAL 1 QUARTER instead of INTERVAL '1 QUARTER'

I can't do a simple query_str.replace("'", str()) because my query contains CAST('inf' AS FLOAT64) So my current solution is some regex to strip the ' specifically from INTERVAL statements re.sub("(INTERVAL '[0-9]+ [A-Z]\w+')", lambda x:x.group(0).replace("'", str()), query_str)