3

How can I do a MySQL date function in Drupal? I have this now:

$select = db_select('someTable', 'st');
$select->addExpression('DATE_FORMAT(FROM_UNIXTIME(created), "%Y-%m")', 'createdMonth');

$date = $select->execute()->fetchAll();

When I run this I get an exception that says:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column '%Y-%m' in 'field list'"

But if I print the $select object I see it should run

SELECT DATE_FORMAT(FROM_UNIXTIME(created), "%Y-%m") AS createdMonth FROM {someTable} st

And when I copy this to Sequel Pro it just runs.

What am I missing?

1

1 Answer 1

7

You're a 'victim' of MySQL's ANSI_QUOTES SQL mode:

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

Emphasis mine.

That explains why MySQL is trying to find a column called '%y-%m' rather than interpreting it as a string.

So you need to switch your SQL mode to something else, or (more reasonably), just use single quotes for literals:

$select->addExpression("DATE_FORMAT(FROM_UNIXTIME(created), '%Y-%m')", 'createdMonth');

Many other DBMSs don't accept double quotes for string literals, so using single quotes is a good habit to get into anyway.

1
  • I think the Drupal coding standards suggest to use the double quotes for SQL strings exactly to avoid this problem. It has been a while since I was approving the CVS applications to become maintainer, so the coding standards could be changed, in the while. :D
    – apaderno
    Aug 31, 2014 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.