Support for INTERVAL operation

Hi,

I'm wondering if PHQL supported DATE operations using INTERVAL because I got an exception using this :

if(FailedLogin::count(array(
    'attempt >= CURRENT_TIMESTAMP - INTERVAL \'1 day\' AND attempt <= CURRENT_TIMESTAMP + INTERVAL \'1 day\''
)) >= 5)

Here is the exception :

Phalcon\Mvc\Model\Exception : Syntax error, unexpected token STRING(1 day), near to ' AND attempt <= CURRENT_TIMESTAMP + INTERVAL '1 day'', when parsing: SELECT COUNT(*) AS rowcount FROM [Nebula\Models\FailedLogin] WHERE attempt >= CURRENT_TIMESTAMP - INTERVAL '1 day' AND attempt <= CURRENT_TIMESTAMP + INTERVAL '1 day' (166)

Back trace says : Phalcon\Mvc\Model\Query->parse()



83.4k
edited Jun '14

This syntax is not supported because it's a MySQL extension that cannot be translated by PHQL when using PostgreSQL/SQlite/Oracle. You can use a raw sql query in this case: http://docs.phalconphp.com/en/latest/reference/phql.html#using-raw-sql

@Phalcon - I think you meant to say it's a MySQL extension that CANNOT be translated by PHQL



83.4k

@quasipickle, you're right, thank you



3.1k

Thanks for the answer. I thought this was part of the SQL standard because Oracle and PostgreSql both understand this syntax too.



83.4k

Implementation is different in PostgreSQL:

INTERVAL '100 days'
INTERVAL '2 months'

vs mysql:

INTERVAL 100 DAY
INTERVAL 2 MONTH

In Oracle, Interval is used to convert a date/datetime/time into another date/datetime/time:

INTERVAL ' 11:12:10.1234567' hour to second


5.4k
edited Jun '14

It would be nice if Phalcon could give informative error messages in such cases? (Something like: "INTERVAL is not supported, please use...")

(And in fact, it could be supported, just the PHQL way of writing it should be inventend for it / decided upon?)



643

For Postgresql i have successful attempt to use intervals with function justify_interval

d.created > totimestamp('2014-05-01', 'YYYY-MM-DD') + justifyinterval('20 day')

I hope this is useful.