Formating a date from database query

Question

I have a query on a Joomla database which works ok except that the date from the "modified" field is not in the desired format. It's presented as 2021-04-30 01:09:56 and I would like it as April 30, 2021. Can someone enlighten me on how to format the $row['modified'] to achieve the desired result.

my query code is:

<?php
defined('_JEXEC') or die('Restricted Access');
use Joomla\CMS\Factory;
$db = Factory::getDbo();
$me = Factory::getUser();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'title' ,'modified')))
->from($db->quoteName('#__content'))
->where($db->quoteName('id') . ' = 114');
$db->setQuery($query);
// echo $db->replacePrefix((string) $query);
$results = $db->loadAssocList();
foreach ($results as $row) {
 echo "<p>" . $row['id'] . ", " . $row['title']. ", " . $row['modified']. "<br></p>";   
}

Does this answer your question? [Convert one date format into another in PHP](https://stackoverflow.com/questions/2167916/convert-one-date-format-into-another-in-php) — El_Vanja, May 01 '21 at 23:22
No not really ... I've tried those formatting options and can't get it to work. I don't think the $row['modified'] is really a variable. If it is, I can't figure out to apply the formatting... I've spent loads of time trying. — rbur, May 01 '21 at 23:40
@rbur Joomla has specialized date considerations and helper functions. Please ask all of your Joomla questions on [joomla.se] Stack Exchange to have a better chance of receiving support from people who _actually_ use Joomla. That said, you probably just want to format your date value in your query with `DATE_FORMAT()`. Joomla is built on the MVC design -- this means we shouldn't be seeing html echoed in your querying script. — mickmackusa, May 02 '21 at 12:35

score 0 · Accepted Answer · answered May 02 '21 at 00:24

<?php
defined('_JEXEC') or die('Restricted Access');
use Joomla\CMS\Factory;
$db = Factory::getDbo();
$me = Factory::getUser();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id', 'title' ,'modified')))
->from($db->quoteName('#__content'))
->where($db->quoteName('id') . ' = 114');
$db->setQuery($query);
// echo $db->replacePrefix((string) $query);
$results = $db->loadAssocList();
foreach ($results as $row) {
    $newDate = date('F j,Y', strtotime($row['modified']));
    echo "<p>" . $row['id'] . ", " . $row['title']. ", " .$newDate. "<br></p>";   
}

This is the solution for your problem. Check this for more informations about this

https://www.php.net/manual/de/function.strtotime.php

https://www.php.net/manual/de/function.date.php

"That's exactly it !!!" thank you so much. Simple when you know how !! Now... after looking at my results, I see I have a timezone issue. Editing the article today shows as 2021-05-01 20:19:00 which is correct but the query shows it as 2021-05-02 01:19:00. I'm guessing I can change this somehow. — rbur, May 02 '21 at 01:32

Formating a date from database query

1 Answers1