MATLAB MySQL query datetime column type

When you do a SELECT * in MySQL query from MATLAB, you would expect all the entries in the table to be returned.

Apparently, there are cases where it will not return everything. (Matlab R2013a, MySQL 5.5.32)

It seems that if you have '0000-00-00 00:00:00' in the datetime field of a row, which is the zero value of the datetime type, the row will not be returned even if you do the select all query.

However, if you are querying other columns (besides the datatime column), the row that has the zero value datetime will be returned.

Connecting to (local) MySQL database in MATLAB

If you try to connect to a local MySQL database based on the Matlab documentation on the database function (see connection example here), you are likely to get the 'Unable to find JDBC driver' error. 

To fix the error, first download the JDBC driver from MySQL website. 

http://dev.mysql.com/downloads/connector/j/

Once download, unzip and find the mysql-connector-java-5.0.8-bin.jar (or more current version) file and move it to Matlab's java path ($matlabroot\java\jar\). 

Next, type prefdir in MATLAB and find your preferences folder, once in that folder, create a text file called javaclasspath.txt, and place the $matlabroot\java\jar\name_of_the_jar_file in and save the file, restart Matlab.

Now if you type javalclasspath, you should see the jar file appear at the bottom of the static path.

Finally, to test out if everything works as expected, run 

conn = database('mysql', 'root', 'DB_password', 'com.mysql.jdbc.Driver',  'jdbc:mysql://localhost/');

conn

tables(conn)


close(conn);

* May need to adjust database name, user account, password, driver name, and url of the database.