EXCEL and MySQL date format conversion

In the MySqL database, the storage type of the date field is an integer field, which stores the number of seconds from January 1, 1900 to the present. If you use data exported from MYSQL in EXCEL, or use ODBC to directly access the MySQL database, you need to use the following conversion formula. " "In the MySqL database, the storage type of the date field is an integer field, which stores the number of seconds from January 1, 1900 to the present. If you use data exported from MYSQL in EXCEL, or use ODBC to directly access the MySQL database, you need to use the following conversion formula.

excel’s date and time field = 25569 + mysql’s date and time field * 0.000011574362151665200

25569 means January 1, 1970 in the excel date. It is not clear why the conversion function uses this date as a parameter. .

For example, the MySQL time of 1024575565 is calculated to be 37427.80864. Then if you set the format of the cell to the standard date and time format, it will be displayed as: 2002-6-20 19:24

Related knowledge: EXCEL Medium date storage: Excel stores all dates as integers and all times as decimals. With this system, Excel can add, subtract, or compare dates and times as numbers, and change all dates through this system.

In this system, serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimals from .0 to .99999, where .0 represents 00:00:00 and .99999 represents 23:59:59. Date integers and time decimals can be combined into numbers that contain both decimal and integer parts.

For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.