WHERE year(CURRENT_TIMESTAMP) - how to do this!?!?

Wondering if CURRENT_TIMESTAMP works in the models, why not do 'year(cdate) = ' .date("Y") . ' ?

I need to use the database server's time. It is a separate server from our API. Accuracy is important. Using the year function was only for this simplified example; the real request is more granular.

How about dialect extension?

namespace Extensions;

class IsCurrentYear
{
    /**
     * @return \Closure
     */
    public function getFunction()
    {
        return function (Dialect $dialect, $expression) {
            $arguments = $expression['arguments'];
            return sprintf(
                " YEAR(%s) = YEAR(CURRENT_TIMESTAMP)",
                $dialect->getSqlExpression($arguments[0])
            );
        };
    }
}

namespace Dialect;

class Mysql extends \Phalcon\Db\Dialect\Mysql
{
    /**
     * Mysql constructor.
     */
    public function __construct()
    {
        $this->registerCustomFunctions();
    }

    /**
     * Register Custom dialect functions
     */
    public function registerCustomFunctions()
    {
        $customFunctions = [
            'IS_CURRENT_YEAR' => 'IsCurrentYear'
        ];
        foreach ($customFunctions as $key => $value) {
            $className = 'Extensions\\'.$value;
            /** @var DialectFunctionInterface $object */
            $object = new $className;
            $this->registerCustomFunction($key, $object->getFunction());
        }
    }
}

interface DialectFunctionInterface
{
    public function getFunction();
}

You need to update your db service:

use Dialect\Mysql as MysqlDialect;
use Phalcon\Db\Adapter\Pdo\Mysql;

$di->set('db', function() {
    return new Mysql([
         'host'     => $config->database->host,
         'username' => $config->database->username,
         'password' => $config->database->password,
         'dbname'   => $config->database->dbname,
         'options'  => [
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_STRINGIFY_FETCHES  => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
         ],
         'charset'  => $config->database->charset,
        'dialectClass' => MysqlDialect::class
    ]);
})

And usage:

$users = User::find('IS_CURRENT_YEAR(cdate)');

Quite diffrent approach but i think it's nice and even better.

That's a hell of a lot of work to use something that [feels like] it should work out of the box. Espcially when you consider that I'm also comparing the month and day of the date.

For what it's worth, this works:

$builder->andWhere('year(User.cdate) = year(now())');

But this doesn't:

$builder->andWhere('year(User.cdate) = year(CURRENT_TIMESTAMP)');

To confuse things further, you cannot use interval:

$builder->andWhere('year(User.cdate) = year(now() - INTERVAL 7 DAY)');

But you can use subdate:

$builder->andWhere('year(User.cdate) = year(subdate(now(), -7))');

Seems like PHQL is choking on the MySql constants, but allows MySql functions. This is inconsistent and confusing, because those functions are NOT part of the standard MySql dialect (they are MySql specific).

you can use mysql stored procedures

https://medium.com/@dragosdm/working-with-mysql-stored-procedures-in-phalcon-ecce7a51ee72

Phalcon uses PDO to talk to databases, so maybe the issue is that PDO has inconsistent support?

If possible, turn on the general query log and see what these queries look like when they're actually sent to MySQL. Perhaps something is getting escaped or quoted that shouldn't be.

No. But phalcon has PHQL - level above on SQL - this is why interval doesn't work too, because it's not recognized in PHQL. Interval is part of Mysql, in some other SQL engiens like MSSQL or Postgresql it doesn't have to work. Same is with CURRENT_TIMESTAMP. This is why the best way to solve this problem is dialect extension. It's not really many work, you can have one class, like only this dialect, what i posted it's how it looks in my app like each function is in seperated class.

The same is in DOCTRINE - same queries will not work until you will add syntax extension.