Change date and date time format in Mysql

Asked

Viewed 2,893 times

-1

I do not want code to send the date converted to Mysql, I want to know if it is possible to show the date as 09/05/2018 09:59:54 instead of 2018-05-09 09:59:54.

It is possible to change this in some configuration?

php mysql phpmyadmin

  • Is there really need to be saved like this? Wouldn’t it be better to format just for display?

  • @Weessmith I think when he said "I don’t want code to send the converted date", he meant he didn’t want to save but just display converted (and that’s the right thing to do even)

  • 1

    @Wallacemaxters, to traveling here kkkkk

  • Type I need to convert to receive in the database and then convert to display, so if this operation is possible are a few less codes and gets more standardized in DB

  • Take a look at this article: configure Mysql to display dates in English I think it might help you.

2 answers

6


First of all, I agree with @Wallace that the ideal is to treat in the application, but as you asked, the answer would be:

  1. You can’t change natively
  2. But you can change the language of the functions that show in full

The documentation is your friend:

https://dev.mysql.com/doc/refman/5.7/en/locale-support.html

The date language is controlled by the variable lc_time_names, but THIS DOES NOT CHANGE THE ISO FORMAT, only the tongue!

Example taken from manual:

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');
+-----------------------------------------+
| DATE_FORMAT('2010-01-01','%W %a %M %b') |
+-----------------------------------------+
| Friday Fri January Jan                  |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SET lc_time_names = 'es_MX';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| es_MX           |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME('2010-01-01'), MONTHNAME('2010-01-01');
+-----------------------+-------------------------+
| DAYNAME('2010-01-01') | MONTHNAME('2010-01-01') |
+-----------------------+-------------------------+
| viernes               | enero                   |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

3

If you are using PHP, you can use the database date and display it in the desired format:

$date = new DateTime($data_do_banco);

echo $date->format('d/m/Y H:i:s');

But it is also possible to do this by select:

 SELECT id, nome, DATE_FORMAT(data, "%d/%m/%Y H%/%i/%S") as data_formatada FROM table

  • i want to change the format inside the bank

  • @Joãopedromorais para que? what is the need for this? There has to be a reason to want a date out of Mysql format

  • Don’t just convert the view, friend? Or do you want to change the way PHPMYADMIN displays the date? [if it is the latter, he should have specified this in the question]

  • To avoid having to keep on converting dates, I work with many date fields and this is boring

  • This I want to change the way PHPMYADMIN displays the date!

  • Young man, that depends on the way you’re working. I work with Laravel for example, and I don’t convert anything that’s in the bank. I leave the Model formatting the date by default in a Brazilian format.

  • then I am converting so $var->format("Y-m-d") then but wish I could send the direct value

  • @Joãopedromorais you use "php in hand" or use some framework?

  • 1

    Note: Changing the display date on PHPMYADMIN will not change the date on your system. What you should do is a standardization and not a bar forcing! You should standardize with functions or settings the desired format.

  • 1

    Given the +1, I only posted a complement to the functions that use extensive.

Show 5 more comments

Browser other questions tagged php mysql phpmyadmin

You are not signed in. Login or sign up in order to post.