A brief discussion on the datetime format when exporting table data from MySQL to Excel

Recently I used MySQL to export table data to an Excel file. The datetime type in MySQL was exported to Excel (Excel 2016) and was recognized by Excel as its own default date format. The format in MySQL is like yyyy-mm-dd hh:mm:ss, but it becomes yyyy/m/dh:mm in Excel, which does not look familiar. Of course, I can change it to a custom format yyyy-mm-dd hh:mm:ss by setting the Excel cell format, but this adds an extra step. Can I directly export from MySQL to Excel in the style displayed by MySQL? sure.

At first, I guessed that because the field in MySQL is of datetime type, Excel automatically converted it to its date type after exporting it to Excel, so I could convert the datetime to a string through MySQL's date_format function, thinking there would be no problem. The result was the same, and I guessed that Excel would also recognize this standard date string format as a date format, so I added a string date in the export statement, which verified my guess. So I thought about breaking this default date format but making it look the same. So when converting it to a string using date_format, I added a space in front, which solved the problem perfectly.

Test tables and test data

CREATE TABLE `users` (
 `username` varchar(255) NOT NULL,
 `create_time` datetime NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
# Insert test data INSERT INTO `users`(`username`, `create_time`) VALUES 
 ('Li Si', '2018-10-11 15:54:23'),
 ('Zhang San', '2018-10-12 15:54:14');

Export the excel statement, note that a space is added before the format in the DATE_FORMAT function (because the table format is utf8, to ensure that there is no garbled code after opening excel, the format needs to be converted to gbk)

SELECT
 username,
 DATE_FORMAT( create_time, ' %Y-%m-%d %H:%i:%s' ) 
FROM users 
 INTO OUTFILE '/tmp/user_info.xls' 
 CHARACTER SET gbk;

You are done. The exported Excel will no longer recognize the date as a date format, but will be in text format, so you can display the date in the style you want.

Supplementary knowledge: Solution to the problem where the date becomes 0000-00-00 when importing Excel files into Navicat Premium

question

In some scenarios, you need to import local files into Navicat. The problem I encountered today is that after successfully importing the Excel file, a date field that was originally correct in Excel became "0000-00-00 00:00:00" in Navicate, which is really incredible.

analyze

After observation, it was found that the date field did not seem to be fully displayed in Excel. For example, it was originally 2018/10/1 0:01:42, but it was displayed as 01:42.0 in Excel. So I tried to modify the cell format of the column in Excel and re-import it into Navicate, and the problem was solved. See below for detailed solutions.

Workaround

1. Before importing, modify the cell format in Excel, set it to custom, type yyyy/m/dh:mm:ss, and save the file.

2. Re-import the file into Navicate.

The above article briefly discusses the datetime format issue when exporting table data from MySQL to Excel. This is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.