to_char function is giving syntax error

0
votes

In the below query, I have used strftime function to get the date in string format. It worked fine in sqlite database. But when the database is changed to progress it threw an error that "SQLSTATE[42883]: Undefined function: 7 ERROR: function strftime(unknown, timestamp without time zone) does not exist"

The below code worked in SQLite and threw an error in Postgres database

public function testArchiveSidebar()
{
    $archives = Question::query()->selectRaw('strftime(? ,created_at)as year,
        strftime(?,created_at) as month,count(id) as qcount' ,['%Y','%m'])
        -> groupBy('year','month')
        ->orderByDesc('created_at')
        ->get();
    $this->assertTrue($archives->count()>0);
}

So, I used the to_char function instead of strftime. However, it is throwing the error.

SQLSTATE[HY000]: General error: 1 no such column: YYYY (SQL: select to_char(created_at ,YYYY) year, to_char(created_at ,Mon) month from "questions" group by "year", "month" order by "created_at" desc)

Query using to_char

public function testArchiveSidebar()
    {
        $archives = Question::query()->selectRaw('to_char(created_at ,YYYY) year,
                    to_char(created_at ,Mon) month,count(id) as qcount')
            -> groupBy('year','month')
            ->orderByDesc('created_at')
            ->get();
        $this->assertTrue($archives->count()>0);
    }

Can anyone help me with this?

3
postgresqlsqliteto-char

3 Answers

1
votes

You need to pass literal:

to_char(created_at, YYYY)     -- before
=>
to_char(created_at, 'YYYY')   -- after
to_char(created_at ,'Mon')
0
votes

The second argument to_char takes is a string - you need to surround it with quotes:

$archives = Question::query()->selectRaw("to_char(created_at, 'YYYY') year,
                to_char(created_at, 'Mon') month, count(id) as qcount")
0
votes

It's working with the below query. Thanks to all for providing your insights.

 $archives = Question::query()->selectRaw("to_char(created_at, 'YYYY') as year,
                to_char(created_at, 'MM') as month,count(id) as qcount")
            -> groupBy('year','month')
            ->orderByRaw('min(created_at) desc')
            ->get();