Jump to content

Time date format issue


GeorgeCScott
 Share

Recommended Posts

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 by GeorgeCScott
Link to comment
Share on other sites

  • GeorgeCScott changed the title to Time date format issue

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 |
+---------------------------------+---------------------+------------+

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.