SQL Timestamp will millisecond precision

[Neil Pate]

Hi guys,

I need to be able to insert and retrieve a timestamp into an SQL database with miliisecond precision (ideally three digits of precision).

Complicating the issue is that my final system is going to be hosted in a MS-SQL (so T-SQL) DB, but I am testing locally using a LAMP stack.

I have done quite a bit of digging, it seems T-SQL would like me to use datetime2 type, but this is not available in MySQL. Apparently I can should use DATETIME(3) as the type. I am struggling to create a table with this as a valid column. I am using a LAMP VM appliance which I believe is running the a version of MySQL that supports fractional timestamps, but the SQL statement to create the table is failing.

CREATE TABLE t1 (t dt DATETIME(6));

Any ideas what I am doing wrong?

Secondly, it looks like the NI DB Toolkit has very primitive support for timestamps, and people prefer to use strings? Can anybody share their current "best practice after having done it wrong a few times" with me? Beer on me for solutions 

Thanks 

 

 

 

 

[Neil Pate]

Update: looks like my LAMP VM appliance is actually shipping with MySQL 5.5 which does not supprt fractional timestamps.. Updating at the moment... 

[Neil Pate]

Finally upgraded and of course the command to create the table did not work... because I had a mistake in.

CREATE TABLE t1 (t dt DATETIME(6));

The "t" above was the remname of another column I had deleted from my query to simplify it, but obvsiously did not delete enough of it.

Should be: CREATE TABLE t1 (dt DATETIME(6));

Still don't know if this has worked but getting somewhere slowly.

[smithd]

Wow I had no idea fractional times were so recent in mysql. Thats crazy.

As for timestamps I had no issues using the standard db connectivity toolkit with timestamps at up to 1 ms of precision (using var to data) but if you get it as a string the format is different from that returned by the database itself, if I remember correctly. We were using both the odbc driver and the tcp driver and the string formats were different in some way. Or maybe it was something with time zones? I could see that being an issue if you let labview handle the conversion for you.

[Neil Pate]

2 hours ago, smithd said:

Wow I had no idea fractional times were so recent in mysql. Thats crazy.

As for timestamps I had no issues using the standard db connectivity toolkit with timestamps at up to 1 ms of precision (using var to data) but if you get it as a string the format is different from that returned by the database itself, if I remember correctly. We were using both the odbc driver and the tcp driver and the string formats were different in some way. Or maybe it was something with time zones? I could see that being an issue if you let labview handle the conversion for you.

Using the standard DB toolkit I cannot yet retrieve the fractional part using, but this could just be me doing something wrong as I have seen threads (like this) which states it should be possible. I have now confirmed using the mysql tools on the server that I can insert and retrieve fractional seconds using the DATETIME(n) type where n is the number of decimal places.

Will do some more digging tomorrow.

[Neil Pate]

Hmm still not really making any progress with this. My DB definitely has millisecond data in it. I have a table t1 with a single column of type DATETIME and have two records in this table, both with millisecond precision. If I execute the query from the server I get back the milliseconds, but the same query from within LabVIEW drops the milliseconds.

It seems this is an issue with the OBDC driver, but I am not sure how to solve this. It has been suggested to perform an on-the-fly conversion to text as per my previously linked thread, but I cannot get this query to work properly.

Any ideas?

 

 

[Neil Pate]

Just stumbled on the toolkit from SAPHIR (thanks smithd for the heads-up in the other forum post). Pure TCP/IP implementation.

http://forums.ni.com/t5/Example-Program-Drafts/Native-LabVIEW-TCP-IP-Connector-for-mySQL-Database/ta-p/3496603

http://www.saphir.fr/en/produits/gdatabase-for-mysql-7.html

Worked first time 

Now I just need to figure out if it is worth $700 , and I have just realised that this is MySQL specific and I need to actually interface with MS-SQL for my real system.

As an aside I am pretty shocked at how simple a basic TCP/IP connector is. 

Edited January 25, 2017 by Neil Pate

[Rolf Kalbermatter]

There are certainly problems with storing and retrieving fractional seconds from database timestamps and that depends on database, according database driver and such. We had many trouble with that on MS-SQL and Oracle in the past and the only thing that works reliably across various versions of databases is to use stored procedures that take either a custom number format or the fractional and second part as separate numbers and then using DB specific functions to combine the two values into a native timestamp. Both ODBC and ADO/DAO lack an unified standard for this that all database drivers would support and traditionally the timestamp was only supporting full second resolution in ODBC and accordingly ADO, as well as in most database servers.

You can't really blame the database toolkit for this, since it is really a pretty thin wrapper around ADO, and can't make up for historical shortcomings of the underlying infrastructure.

As to implementing a native T-SQL protocol through TCP/IP VIs, there is at least one library out there that is definitely not as extensive and well tested as the Saphir toolkit for MySQL but also workable. The problem about the T-SQL protocol or more precisely TDS is that it is not fully documented. The Open Source implementation in C, called FreeTDS is based in part on an older public specification of an older Sybase SQL server version which MS-SQL server is derived from. That documentation is for version 4.2 of the TDS protocol but the current MS-SQL Server versions use version 7.4. MS has added various extensions to it since the 4.2 version and current MS SQL Servers refuse to connect with a TDS client that doesn't support at least 7.0.

Quite a bit of the 5.0 and higher support in FreeTDS was basically reverse engineered through network logs and as such can be considered working for many cases but likely isn't fully protocol compliant. While one can implement a native LabVIEW library for the TDS protocol using the TCP/IP VIs, this would have to be based in large parts on the openly available protocol documentation of the TDS 4.2 specification with extra info from the preliminary protocol description in the FreeTDS documentation, possibly helped by peeks into the FreeTDS source code. But that source code is under GPL license, so looking to much at that code is not a good idea to implement a non GPL implementation of said protocol. Also an additional problem with trying to implement this in pure LabVIEW is the fact that newer protocol versions add various encryption and compression features, that are not easily implemented in pure LabVIEW.

Edited January 28, 2017 by rolfk

[Neil Pate]

Got this solved "hopefully". I gave up on trying to mimic this in MySQL as my actual system will use MS-SQL anyway. I rolled out a MS SQL instance in AWS and everything pretty much just worked. Specifying the datatype for a column as datetime2 allows me to insert and query with precision of 7 decimal places :-)