<?php



namespace DoctrineExtensions\Tests\Query\Sqlite;



use DoctrineExtensions\Tests\Query\SqliteTestCase;



class DateFormatTest extends SqliteTestCase

{

    public function testCommonFormats()

    {

        // Both result in '2015-12-30 14:23:40'

        $dql = "SELECT DATE_FORMAT(b.created, '%Y-%m-%d %H:%i:%S') FROM DoctrineExtensions\\Tests\\Entities\\BlogPost b";



        $this->assertEquals(

            "SELECT STRFTIME('%Y-%m-%d %H:%M:%S', b0_.created) AS {$this->columnAlias} FROM BlogPost b0_",

            $this->entityManager->createQuery($dql)->getSql()

        );

    }



    /**

     * Because sqlite does not support as many substitutions as mysql,

     * they get converted to the closest sqlite substitutions.

     * In this test a few examples are listed:

     */

    public function testUnsupportedFormats()

    {

        // Sqlite does not support AM / PM

        // mysql: '10:15:22 PM'

        // sqlite: '22:15:22'

        $dql = "SELECT DATE_FORMAT(b.created, '%r') FROM DoctrineExtensions\\Tests\\Entities\\BlogPost b";



        $this->assertEquals(

            "SELECT STRFTIME('%H:%M:%S', b0_.created) AS {$this->columnAlias} FROM BlogPost b0_",

            $this->entityManager->createQuery($dql)->getSql()

        );



        // This is the worst case: Sqlite does not support ISO week numbers, so they get converted to %Y-%W

        // Mysql: "select date_format('2016-01-01', '%x-%v')" results in '2015-53'

        // Sqlite: "select date_format('2016-01-01', '%x-%v')" results in '2016-01'

        $dql = "SELECT DATE_FORMAT(b.created, '%x-%v') FROM DoctrineExtensions\\Tests\\Entities\\BlogPost b";



        $this->assertEquals(

            "SELECT STRFTIME('%Y-%W', b0_.created) AS {$this->columnAlias} FROM BlogPost b0_",

            $this->entityManager->createQuery($dql)->getSql()

        );



        // Sqlite does not support weekday names, month names or month days with English suffix.

        // They all get converted to their numeric representations

        // Mysql: "Tuesday, November 10th, 2015"

        // Sqlite: "2, 11 10, 2015"

        $dql = "SELECT DATE_FORMAT(b.created, '%W, %M %D, %Y') FROM DoctrineExtensions\\Tests\\Entities\\BlogPost b";



        $this->assertEquals(

            "SELECT STRFTIME('%w, %m %d, %Y', b0_.created) AS {$this->columnAlias} FROM BlogPost b0_",

            $this->entityManager->createQuery($dql)->getSql()

        );

    }

}

