Diff years between two dates. I can not use TIMESTAMPDIFF in Query Builder

How can I calculate diff years between 2 dates using mysql function timestamdiff in my query builder? I tried to create sql custom dialect funtion but it doesn't work:

$dialect->registerCustomFunction(
    'DATE_DIFF_YEARS',
    function ($dialect, $expression) { 
        $arguments = $expression['arguments'];
        return sprintf(
            "TIMESTAMPDIFF(YEAR, %s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);

I'm not sure but I don't think TIMESTAMPDIFF() is part of PHQL dialect in this case you have to add in mysql dialect, see this incubator

Good luck

What you mean it doesn't work? It should work without any problem, what exactly issue you have?



204
edited May '18

I want to use like this

$results =\Phalcon\Di::getDefault()->getModelsManager()->createBuilder()
           ->columns([
               'age' => 'TIMESTAMPDIFF(NOW() ,m.birthdate)'
           ])
           ->addFrom('Models\Members', 'm');

and I get this error:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW(), `m`.`birthdate`) AS `age` FROM `members` AS `m` LIMIT 5 OFFSET 0'

MYSQL version is 5.7.21

But how did you register your db service?



204
edited May '18

from services.php

use Phalcon\Db\Adapter\Pdo\Mysql as DbAdapter;

$di->set('db', function () use ($config) {
    $dbConfig = $config->database->toArray();
    $dbConfig['dialectClass'] = require $config->application->configDir . 'sql.dialect.php';
    $connection = new DbAdapter($dbConfig);
    return $connection;

and the sql.dialect.php

<?php
use Phalcon\Db\Dialect\MySQL as SqlDialect;

$dialect = new SqlDialect();

$dialect->registerCustomFunction( 'DATE_DIFF_YEARS', function ($dialect, $expression) {

    $arguments = $expression['arguments'];
    return sprintf(
        "TIMESTAMPDIFF(YEAR, %s, %s)",
        $dialect->getSqlExpression($arguments[0]),
        $dialect->getSqlExpression($arguments[1])
    );
});

return $dialect;


204

I think I need to add this line to db configution 'dialectClass' => \Phalcon\Db\Dialect\MysqlExtended::class,

Yes, most likely. I mean this class needs to be your class obviously. If you are using class from incubator then it's most likely it.