Cannot Add Date/Time Column to MySQL Database Using Database Connectivity Toolkit

Updated May 8, 2023

Reported In

Software

  • LabVIEW 2017 Professional
  • LabVIEW 2016 Professional
  • LabVIEW 2015 SP1 Professional
  • LabVIEW Database Connectivity Toolkit
  • LabVIEW 2017 Database Connectivity Toolkit
  • LabVIEW 2016 Database Connectivity Toolkit
  • LabVIEW 2015 SP1 Database Connectivity Toolkit

Issue Details

I'm using Database Connectivity Toolkit to write data to a MySQL database from LabVIEW. I need to create a table containing a column of date/time datatype, thus I used DB Tools Insert Data VI  (setting create table? input to True) to create the table and DB Tools Format Datetime String VI to return a date/time string to be written to the database:
However, after executing the VI, the table created in the MySQL database only contains the date information and not time information.

Creating the table using DB Tools Create Table VI leads to the same problem.
 

Solution

This is a known issue in LabVIEW 2015 and later identified with ID #575865  which is currently under investigation.

As a workaround, you can:
  1. Create the table using DB Tools Create Table VI  or DB Tools Insert Data VI  (setting create table? input to True) without the date/time column
  2. Use DB Tools Execute Query VI function to add a column of the correct date/time type to the table
Alternatively, you can also directly create the table with the correct date/time column though SQL query, using DB Tools Execute Query VI function, as shown below:

Additional Information

You can search for the known issue ID provided above in the readme of future LabVIEW versions to check if it has been solved.

Database Connectivity Toolkit is included in LabVIEW Professional Development System 2014 and later: no additional purchase is required if using this version.