Sybase – Handling Datetime Fractional Seconds

jdbcsybasesybase-asesybase-ase-15.7

I am reading the description of the datetime datatype from Sybase ASE 15.7 documentation:

datetime columns hold dates between January 1, 1753 and December 31, 9999. datetime values are accurate to 1/300 second on platforms that support this level of granularity.

I find the above very confusing. First of all, this is the only data type for which the caveat "on platforms that support this level of granularity" is added. What exactly does this mean and how do I find if my platform is one of those that "support this level of granularity"?

Moreover, it is not clear to me what being able to accurately store 1/300ths of a second effectively means. I am accessing the database using JDBC for which the only plausible type is java.sql.Timestamp. This type allows me to retrieve up to the precision of nanoseconds. But given that division by 300 requires infinite digits in the decimal system in the general case, effectively a fractional number of nanoseconds (with infinite decimal digits) are needed to hold a value expressed as 1/300 of a second. So, this means that I am not able to obtain the value stored in the server without losing some precision, however negligible.

Finally, when I execute the following query:

SELECT convert(char(32), submission_date, 139) FROM some..table

I see values like the following:

Jan  6 2014 12:36:12.420000     
Sep 12 2013 13:44:57.100000     
Sep 10 2014 13:47:02.240000     
Sep 10 2014 13:47:07.850000     
Sep 10 2014 13:47:13.346000     
Sep 10 2014 13:47:19.033000     
Sep 10 2014 13:47:24.533000     
Sep 10 2014 13:47:30.030000     
Sep 10 2014 13:47:35.636000     
Sep 10 2014 13:47:41.136000     
Sep 10 2014 13:47:46.750000     
Sep 10 2014 13:47:52.240000     
Sep 25 2014 09:01:18.426000

These values seem to indicate that only whole thousandths of a second are kept (not 1/300ths of a second – which would require a fractional number of thousandths). If it were the case that the server internally stores the values "accurately to 1/300 second" I would expect the conversion to a decimal notation to use all available decimal places (except for the edge cases of 3/300 secs, 30/300 secs, 150/300 secs and a few others which do not require an infinite amount of digits in the decimal system).

Best Answer

datetime in Adaptive Server Enterprise (and SQL Server, since they share a common-code-base that included the datetime type) is stored using 8 bytes. 4 bytes for the date, and 4 bytes for the time. You can see this by looking at the binary version of a datetime:

SELECT CONVERT(varbinary(8), CONVERT(datetime, '1753-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-01T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.000'), 0)
SELECT CONVERT(varbinary(8), CONVERT(datetime, '3000-12-31T23:59:59.997'), 0)

The four binary values are:

0xFFFF2E4600000000
0x0000000000000000
0x0000000100000000
0x000622D3018B81FF

Take the following, which shows where the 1/300 of a second comes into play:

SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-01T00:00:00.000'), 0) 
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.000'), 0) 
SELECT CONVERT(varbinary(8), CONVERT(datetime, '1900-01-02T00:00:00.003'), 0)

The difference between the 2nd and 3rd values, is one:

0x0000000000000000
0x0000000100000000
0x0000000100000001

So dates are stored in the most significant 4 bytes; and times are stored in the least significant 4 bytes. Although moving to a precision greater than 3 milliseconds (1/300 of a second) would be possible in 4 bytes of storage; that is all the precision that is actually used.

In SQL Server, you can use a datetime2(7) data type to get precision down to 7 digits, with an accuracy of 100ns:

SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000000'), 0)
SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000001'), 0)
SELECT CONVERT(varbinary(16), CONVERT(datetime2(7), '1900-01-01T00:00:00.0000002'), 0)

The storage of these values are slightly different, however you can still see the binary value incrementing:

0x0700000000005B950A
0x0701000000005B950A
0x0702000000005B950A

I'm using the Sybase ISQL client; Sybase CTISQL Utility/15.7/P-EBF20996 SP100/DRV.15.7.0.10

As an aside, a small difference exists between how Adaptive Server Enterprise (ASE) and SQL Server round datetime values. In SQL Server, milliseconds are rounded to 0.000, 0.003, and 0.007, whereas in ASE they are rounded to 0.000, 0.003, and 0.006 - why there is a difference is not documented as far as I can tell. You can see this on ASE by running this query:

SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 139);

Which returns:

Jan  1 2017 23:59:59.996000

Whereas on SQL Server, the equivalent code, SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 109); , returns:

Jan  1 2017 11:59:59:997PM