0

I created a custom module and printed this outcome in .csv.

Name,Event,Email,Timestamp
Ea Iustum Jus,Fifa,example@asdf.wer,1573924224

How do I convert the timestamp in a date format?

I tried with $select->addField('c','created',"DATE_FORMAT(FROM_UNIXTIME(created), '%d.%m.%Y %H:%i'"); and $select->addField('c', 'created', date('m-d-Y',$select));.

This is the complete code in mymodulecontroller.php.

/**
 * @file
 * Contains \Drupal\mymodulelist\Controller\ReportController.
 * - it works but timestamp needs be converted to human readable date ??
 * - $select->addField('c', 'created');
 */
namespace Drupal\mymodulelist\Controller;

use Drupal\Core\Controller\ControllerBase;
use Drupal\Core\Database\Database;
use Symfony\Component\HttpFoundation\Response;

/**
 * Controller for mymodule List Report in .CSV
 */
class DownloadCSVController extends ControllerBase {

 /**
   * Gets all mymodules List for all nodes.
   *
   * @return array
   */
  protected function load() {
    $select = Database::getConnection()->select('mymodulelist', 'r');
      // Join the users table, so we can get the entry creator's username.
    $select->join('users_field_data', 'u', 'r.uid = u.uid');
      // Join the node table, so we can get the event's name.
    $select->join('node_field_data', 'n', 'r.nid = n.nid');
    // Select these specific fields for the output.
    $select->join('mymodulelist', 'r', 'r.mail = r.mail');
    // Select these specific fields for the output.
    $select->join('mymodulelist', 'c', 'r.created = c.created');
    // Select these specific fields for the output.
    $select->addField('n', 'title');
    $select->addField('u', 'name', 'username');
    $select->addField('r', 'mail');
    // how to create a date format from unix timestamp to d-m-Y for the field created ?? 
    $select->addField('c', 'created');
    // I tried this 2 options
    // 1. $select->addField('c', 'created',"DATE_FORMAT(FROM_UNIXTIME(created), '%d.%m.%Y %H:%i'");
    // 2. $select->addField('c', 'created', date('m-d-Y',$select));
    $entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);
    return $entries;
  }
}
  • 1
    You can use the AddExpression method. There is already a question around this. drupal.stackexchange.com/questions/219907/… Hope that helps. – anoopjohn Nov 18 '19 at 0:47
  • how to use the AddExpression? – Joehoe Nov 18 '19 at 1:09
  • I added like this in the function$select->addExpression("DATE_FORMAT(FROM_UNIXTIME(created), '%d.%m.%Y %H')", 'created'); but get SQL errors – Joehoe Nov 18 '19 at 1:11
  • You already have a created field. Try with another alias. $select->addExpression("DATE_FORMAT(FROM_UNIXTIME(created), '%d.%m.%Y %H')", 'created_formatted'); – anoopjohn Nov 18 '19 at 1:25
  • ah the last argument must be dotted. example: 'c.created_formatted' – Joehoe Nov 18 '19 at 1:50

Browse other questions tagged or ask your own question.