Change date format (in DB or output) to dd mmm yyyy - PHP MySQL

user_19349938 0
Viewed 68

MySQL stores the date in my database as 'YYYY-MM-DD' The field type for my date is 'CREATED_AT' I only using date and not time I want to change it when I open my html table the date must show like this. dd mmm yyyy (27 Jun 2022)

This is my code for my table

    <td><?php echo $checkSqlRow["CREATED_AT"]; ?>

I did search and find this code, but I am not sure where to add it to work.

$timestamp = strtotime($date_from_db);

echo created_at('dd/mmm/YYYY', $timestamp);

html php
3 Answers

I just went through this problem on my companies website last week, it turns out Datetime manipulation is best done with two main principles:

  1. Do not reinvent the wheel, using a library is best to handle things like localization (converting timezones) etc.

  2. Use little of your own electricity by letting your users to process information.

Implementation: Use the moment javascript library. This will handle complex dates operations with a well known api.

Recommendation: Allow MySQL, or other data service, to generate full datetime stamps. It is little overhead as is memory optimized, is better for debugging, can always be generalized to a less specific time, and be be made as the default value (at least for mysql). ie.

create table exampleSchema
    game_id                 bigint unsigned auto_increment
        primary key,
    name                    varchar(255) default ''                not null,
    code                    varchar(255) default ''                not null,
    created                 datetime     default CURRENT_TIMESTAMP not null,
    modified                datetime     default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
) engine = InnoDB;
user_50336010

You can change your date format using this

       $newDate=date("d M Y", $yourDate);
user_190996860

Changing format from database

You can change date format in MySQL while fetching the records. So, you will not need to manipulate things on PHP side. Your desired output will be returned from MySQL.

SELECT *, DATE_FORMAT(CREATED_AT, '%d %b %Y') AS CREATED_AT
FROM your_table;

The statement above will return all the fields with modified created_at field.

Changing format from code

You can use date function to convert your date to a specific format.

$formatted_date = date('d m Y', $your_date);
user_77125650
Related