Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table

mysql datetime command-line format create-table
Iam Zesh picture Iam Zesh · Dec 1, 2011 · Viewed 356.8k times · Source

After googling around, I cannot find a way to create a new table with a DATETIME column with the default format set to 'DD-MM-YYYY HH:MM:SS'

I saw a tutorial in which it was done in phpmyadmin so I suspect that I could use mysql via command line and achieve the same thing when creating my new table with 

CREATE TABLE ()

Thank you in advance

Answer

Mirko Akov picture Mirko Akov · Dec 1, 2011

"MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format." This is from mysql site. You can store only this type, but you can use one of the many time format functions to change it, when you need to display it.

Mysql Time and Date functions

For example, one of those functions is the DATE_FORMAT, which can be used like so:

SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM tablename

Related questions

How do you set a default value for a MySQL Datetime column?

How do you set a default value for a MySQL Datetime column? In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.

Read More
Should I use the datetime or timestamp data type in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)? I'm working with PHP on the server side.

Read More
Convert from MySQL datetime to another format with PHP

I have a datetime column in MySQL. How can I convert it to the display as mm/dd/yy H:M (AM/PM) using PHP?

Read More