Support for MSSQL DATETIME and DATETIME2 Column Types

[JamoCA]

I discovered 2 more datatypes that should to be added to the private LuceeSpreadsheet getQueryColumnFormats() function. I use the Microsoft JDBC SQL driver (it's faster than Adobe's driver & updated more often). I use DATETIME & DATETIME2 column types. Please consider including this wherever DATE & TIMESTAMP data types are used.

While DATETIME2 is more precise, I've noticed that "2017-06-23 18:39:49.1234567" is currently exported as "2017-06-23 18:39:49". In Excel, I can manually enter a custom number format of "yyyy-m-d h:mm:ss.000", but it won't go past 3 decimal places.

I noticed that the time exported is specifically formatted to "HH:MM:SS". Can similar support for DATETIME2 be added to go out to 3 decimal places? (This is the max apparently.)

NOTE: I currently retain this data in ISO text format with the time zone offset due to limitations like this.

[cfsimplicity]

I can easily add formats to the detection rules in getQueryColumnFormats().

But supporting fractions of a second when adding time/datetime values to a workbook looks quite complicated. I'll look into it but can't promise I'm afraid.

[cfsimplicity]

Had a close look at this and it seems that the values inserted by POI do include fractions of a second, but they are stored as numbers (doubles). To display these numeric values as dates/times, the appropriate formatting needs to be applied. You should be able to control this by passing in the TIME and TIMESTAMP masks you want, e.g.

spreadsheet = luceeSpreadsheet( dateFormats={ TIMESTAMP="yyyy-mm-dd h:mm:ss.000" } );

However, formatting support may be limited by the spreadsheet software you're using. I use OpenOffice Calc and tested with 6 decimal points (supported by my DB of choice MariaDB) and it worked ok. MS Excel may only handle 3 as you say (I don't have it to check). Update: POI seems only able to handle up to millisecond precision (see below)

I did find that the TIME format was not being respected when it came to fractions of a second, so have put in a (somewhat complicated) fix for that.

I've added support for DATETIME and DATETIME2 as requested.

Please could you give develop a go to see if these changes are working?

Thanks.

[cfsimplicity]

Here's my test using a MariaDB table with a datetime(6) column and a time(6) column:

dateFormats = {
 TIME: "hh:mm:ss.000000"
 ,TIMESTAMP: "yyyy-mm-dd hh:mm:ss.000000"
};
spreadsheet = New luceeSpreadsheet.spreadsheet( dateFormats );
data = QueryExecute( "SELECT * FROM datetimes" );
spreadsheet.downloadFileFromQuery( data, "test" );

On looking more closely at the 3rd row, it seems that even though my OO Calc is including 6 decimal places, it's only precise to the first 3. Further testing shows that this is a limitation of POI, since this truncated value is what POI is setting in the workbook cell before being written:

wb = spreadsheet.new();
spreadsheet.addRows( wb, data );
writedump( spreadsheet.getCellValue( wb, 3, 1 ) );

So it seems millisecond precision is probably the best we can do.

[JamoCA]

Would setting timestamp when initializing make all timestamps formatted like this? There are columns were I only need minute accuracy and it would be overkill to export millisecond accuracy.

Is there any way to specify this on a per-column basis or is it set once and used for every timestamp?

[cfsimplicity]

It will be for all time/timestamp columns, but you can override it for specific columns using formatColumn() on the generated workbook object:

dateFormats = {
 TIME: "hh:mm:ss.000000"
 ,TIMESTAMP: "yyyy-mm-dd hh:mm:ss.000000"
};
spreadsheet = New luceeSpreadsheet.spreadsheet( dateFormats );
data = QueryExecute( "SELECT * FROM datetimes" );
workbook = spreadsheet.workbookFromQuery( data );
// set a different format for column 1
spreadsheet.formatColumn( workbook , { dataformat: "yyyy-mm-dd hh:mm:ss" }, 1 );
// ... save etc

[cfsimplicity]

In fact it'll probably be simpler in most cases to leave the default dateFormats and format the specific columns that need greater precision. The choice is yours though.