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;
}
}
$select->addExpression("DATE_FORMAT(FROM_UNIXTIME(created), '%d.%m.%Y %H')", 'created');
but get SQL errors – Joehoe Nov 18 '19 at 1:11'c.created_formatted'
– Joehoe Nov 18 '19 at 1:50