We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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()

1


98.9k
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: https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql



125.8k

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



98.9k

@quasipickle, you're right, thank you



5.1k

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



98.9k

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


8.1k
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?)



1.3k

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

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

I hope this is useful.



309