Level 29
It clearly states that the function is not available for sqlite. Look for alternatives http://www.sqlite.org/lang_datefunc.html
May 3, 2016
9
Level 7
DATE_FORMAT() FAILING IN SQLITE
I have a method calculating the number of user signups;
public function getSignupsCount()
{
$users = DB::select('select count(id) as count, DATE_FORMAT(created_at, "%M %y") as months
from users group by months order by created_at');
return $users;
}
My functional tests use Sqlite based database. When testing this method, I get the error; `
SQLSTATE[HY000]: General error: 1 no such function: DATE_FORMAT (SQL: select count(id) as count, DATE_FORMAT(created_at, "%M %y") as months from users group by months order by created_at)
I am wondering whether to change my test DB to Mysql or is there a way to re-write this and retain my existing test database?
Level 7
@d3xt3r , I already deduced this from the error message I was getting, which is why I posted the question seeking solution on the alternatives.
Level 29
Ok, happy to help. Tell us what you have already tried ?
Level 7
I am inclined to change my test database to mysql but wondering if there was a work-around that will enable me retain the sqlite test database. I have tried using strtotime() but was not able to make it work.
Level 29
strftime() ??
Level 50
If your production DB is mysql and you're doing mysql-specific (or at least not DB-agnostic SQL) then you're probably better off using mysql in your tests, imho. Sqlite is great for a lot of things, but not everything. For instance this can bite you in tests vs. production :
sqlite> create table hello (number int);
sqlite> .schema hello
CREATE TABLE hello (number int);
sqlite> insert into hello values(1);
sqlite> select * from hello;
1
sqlite> insert into hello values('yay');
sqlite> select * from hello;
1
yay
1 like
Level 7
Level 7
Apparently strftime() function is available in Sqlite but not in mysql. I don't want to have to call the PHP strftime() method so for now, I will go with @ohffs solution to use mysql for my test db since that is what I am using in production anyway.
Level 2
In many cases the speed of tests is vital.
If you want to stick to in-memory SQLite DB for testing you could use the conditional SQL query construction at runtime replacing SQLite strftime() with MySQL date_format() back and forth based on the value of your env('DB_CONNECTION'). E.g. like:
$dateClause = env('DB_CONNECTION') === 'sqlite' ?
'strftime("%Y-%m-%d", created_at)' :
'date_format(created_at, "%Y-%m-%d")';
Then interpolate $dateClause in your raw query string.
2 likes
Please or to participate in this conversation.