Time date format issue

[GeorgeCScott]

I have a small database which collects the name and time/date. The fields are both text

time     varchar    (255)utf8_general_ci

name   varchar   (255)utf8_general_ci

 

When the date is inserted into the database it is in this format  Mon, 21 Nov 2022 10:05:13 +0200

I want to change it 2022-11-21 in my report

while ($results = $query->fetch()) {

               

                                echo '<tr>';

                                echo '<td>'. $results['name'] . '</td>';

                                echo '<td>'. $results['time'] . '</td>';

                               echo '</tr>';

I have tried to find a solution but seem to get stuck when I try and add it into the actual report. the last one I tried was

"              date("d-m-Y", strtotime($time));               "

Any help would be greatly appreciated

Edited November 21, 2022 by GeorgeCScott

[Barand]

Store dates and times in your DB as date, time or datetime types format yyyy-mm-dd-hh-ii-ss (that's what they are there for). You can then use the dozens of inbuilt datetime functions, sort and compare them. There is, however, a str_to_date() function in mysql to convert a string to a datetime type. Yo can use the date_format() function to convert a datetime field to your required output format (eg d/m/Y).

Example

My original table

select * from deftest;
+---------------------------------+
| test                            |
+---------------------------------+
| Mon, 21 Nov 2022 10:05:13 +0200 |
+---------------------------------+

Using the functions...

select test as original
     , str_to_date(test, '%a, %d %b %Y %H:%i:%s') as asDatetime
     , date_format(str_to_date(test, '%a, %d %b %Y %H:%i:%s'), '%d/%m/%Y') as output
from deftest;

+---------------------------------+---------------------+------------+
| original                        | asDatetime          | output     |
+---------------------------------+---------------------+------------+
| Mon, 21 Nov 2022 10:05:13 +0200 | 2022-11-21 10:05:13 | 21/11/2022 |
+---------------------------------+---------------------+------------+

 

[dodgeitorelse3]

Where is $time defined?