ODBC - Return datetimes in epoch format, for performance

[mmcnabb-vms]

When retrieving a datetime from the database via ODBC: in the TDS wire protocol, datetime are sent in epoch form (number of days since 1900-01-01, and number of ticks since midnight). However, as far as I can see, the only option in the ODBC specification -- and in FreeTDS -- is to retrieve that date as YMD... ; for example SQL_TIMESTAMP_STRUCT, or a char type, or SQL_C_BINARY -- all of them go through tds_datecrack.

In my use case the client application wants dates in epoch form. For performance reasons we'd like the ability to retrieve the date in epoch form, instead of FreeTDS converting it to YMD and the client having to convert back to epoch.

I coded a proof of concept of this (see below), and in a test case involving a cursor fetch of tens of thousands of rows each with about 50 date columns, it improved performance by about 40% on simply fetching all the rows, with dates available in epoch form. So this is definitely a worthwhile change from performance perspective.

My proof of concept was to enable conversion between MS datetime, and 64-bit integer:

index da05a667..ccdf5819 100644
--- a/src/tds/convert.c
+++ b/src/tds/convert.c
@@ -1372,6 +1372,15 @@ tds_convert_datetimeall(const TDSCONTEXT * tds_ctx, int srctype, const TDS_DATET
                cr->bigtime = dta->time / 10u
                              + (dta->date + BIGDATETIME_BIAS) * (UINT64_C(86400) * 1000000u);
                return sizeof(TDS_UINT8);
+        /* MM TODO - Do we want to copy SYB5BIGTIME algorithm for example */
+       case SYBINT8:
+       case SYBUINT8:
+        /* FreeTDS still decomposed the TDS Date into the DATETIMEALL struct,
+         * so here we reassemble it.
+         */
+        cr->bi = ( ((dta->has_date ? dta->date : 0) * 1ULL) << 40 )
+            + (dta->has_time ? dta->time : 0);
+        return sizeof cr->bi;
                /* conversions not allowed */

and adjusted the client to use SQL_C_BIGINT for the binding type, instead of SQL_C_TYPE_DATE or SQL_C_TIMESTAMP. I didn't yet code the same thing for input or for Sybase dates but in principle it would be similar.

Note: bcp -n also reads/writes datetime in epoch format, it does this by having its own conversion function instead of going through tds_convert and friends.

Discussion points / questions:

• Does this seem like the right way to go (enable conversions between datetimes and SYBINT8)? In the ODBC specification it appears to say that conversions (such as this) not covered by the spec are implementation-defined, so it would be permissible for FreeTDS to define this conversion.
  • The code could be moved from tds_convert_datetimeall into odbc_tds2sql() to avoid possible unintended side effects.
• Another option might be to define a driver-specific binding type, e.g. SQL_C_EPOCH_DATETIME or even SQL_C_VMS_DATE; the ODBC specification does reserve a range of type identifiers for driver-specific bindings.
• It could be possible to repurpose SQL_C_BINARY for this purpose. It would break behaviour for anyone currently relying on FreeTDS's ODBC datetime-to-BINARY conversion but that's not documented behaviour anyway.
  • I have tested Microsoft native ODBC Driver 18 ; binding DATETIME to SQL_C_BINARY gives an epoch format output, but binding DATETIME2 to SQL_C_BINARY gives YMDHMS + 100ns tick count. We probably don't want to intentionally behave differently than the MS driver in that respect, so using SQL_C_BINARY for this feature wouldn't be appropriate.

[mmcnabb-vms]

@freddy77 any first impressions on this feature: whether you want it in mainline and if so, whether to do it via binding to SQL_C_SBIGINT, versus defining a driver-specific bind type?

I'm going to do it anyway on a VMS branch as we need that functionality. To keep it simple I'm planning to just use 100ns ticks since 1 Jan 0001, which is a widely used Microsoft format (and easily convertible to other epochs).

[freddy77]

I would use an additional extension type not conflicting with Microsoft ones. A date is not a number (there are also conversion tables) and the binaries are compatible with Microsoft implementation.
The type looks pretty similar to the Windows FILETIME (100ns units although the base reference is different) also we need 7 decimal digits to support DATETIME2(7).
Starting with 1 Jan 0001 would be a mistake, the current calendar started a bit before 1600, earlier the lengths of months and years were different and you can't apply the same math. I would keep SQL Server base (I don't remember if it's 1900 or 1970).

[mmcnabb-vms]

OK. On further investigation there was an alignment fault in my date handling code, so after fixing that, the performance benefit of this change is only around 5% in my test case mentioned earlier. So I am not going to prioritize it this month, but it's still worth doing as that's still a nice performance boost to have .