主页 / dba / 问题 / 166370
Accepted
Marcus Junius Brutus
Asked: 2017-03-07 11:31:56 +0800 CST

datetime 小数秒

  • 772

我正在阅读Sybase ASE 15.7 文档datetime中的数据类型描述：

datetime 列包含 1753 年 1 月 1 日到 9999 年 12 月 31 日之间的日期。在支持这种粒度级别的平台上，datetime 值精确到 1/300 秒。

我觉得上面的内容非常令人困惑。首先，这是唯一添加了“在支持这种粒度级别的平台上”的警告的数据类型。这到底是什么意思，我如何确定我的平台是否是“支持这种粒度级别”的平台之一？

此外，我不清楚能够有效地准确存储 1/300 秒意味着什么。我正在使用 JDBC 访问数据库，唯一可能的类型是java.sql.Timestamp。这种类型允许我检索到纳秒的精度。但鉴于在一般情况下除以 300 需要十进制系统中的无限位数字，实际上需要小数纳秒（具有无限十进制数字）来保持表示为 1/300 秒的值。因此，这意味着我无法在不丢失一些精度的情况下获取存储在服务器中的值，但可以忽略不计。

最后，当我执行以下查询时：

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

我看到如下值：

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

这些值似乎表明只保留了整千分之一秒（而不是 1/300 秒 - 这需要千分之一的小数）。如果是服务器内部存储值“精确到 1/300 秒”的情况，我希望转换为十进制表示法以使用所有可用的小数位（3/300 秒的边缘情况除外，30/300 secs、150/300 secs 和其他一些不需要十进制系统中无限位数的数字）。

sybase jdbc

2 个回答

  1. Best Answer

    datetime在 Adaptive Server Enterprise（和 SQL Server，因为它们共享一个包含 datetime 类型的公共代码库）中，使用 8 个字节进行存储。4 个字节为日期，4 个字节为时间。您可以通过查看日期时间的二进制版本来看到这一点：

    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)

    四个二进制值是：

    0xFFFF2E4600000000
0x0000000000000000
0x0000000100000000
0x000622D3018B81FF

    采取以下，它显示了 1/300 秒的作用：

    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)

    第二个和第三个值之间的差异是一个：

    0x0000000000000000
0x0000000100000000
0x0000000100000001

    所以日期存储在最重要的 4 个字节中；和时间存储在最低有效的 4 个字节中。尽管在 4 个字节的存储空间中移动到大于 3 毫秒（1/300 秒）的精度是可能的；这就是实际使用的所有精度。

    在 SQL Server 中，您可以使用datetime2(7)数据类型将精度降至 7 位，精度为 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)

    这些值的存储方式略有不同，但是您仍然可以看到二进制值递增：

    0x0700000000005B950A
0x0701000000005B950A
0x0702000000005B950A

    我正在使用 Sybase ISQL 客户端；Sybase CTISQL 实用程序/15.7/P-EBF20996 SP100/DRV.15.7.0.10

    datetime顺便说一句，Adaptive Server Enterprise (ASE) 和 SQL Server 舍入值的方式之间存在细微差别。在 SQL Server 中，毫秒被四舍五入为0.000、0.003和0.007，而在 ASE 中，它们被四舍五入为0.000、0.003和0.006- 据我所知，没有记录为什么存在差异。您可以通过运行以下查询在 ASE 上看到这一点：

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

    返回：

    Jan  1 2017 23:59:59.996000

    而在 SQL Server 上，等效代码SELECT CONVERT(varchar(50), CONVERT(datetime, N'2017-01-01T23:59:59.997'), 109);, 返回：

    Jan  1 2017 11:59:59:997PM
    • 7

  2. 基于这个答案，很明显，确切的编码是不透明且违反直觉的。同样清楚的是，Sybase 文档中关于datetime可以保存值的声明accurate to 1/300 second具有误导性和不精确性。

    文档应该说的是datetime分辨率/精度/粒度（选择您最喜欢的术语）大约为 1/300 秒或大约 3 ms。

    我做了一个测试来证实这一点。在一个大约有 1000 个datetime值的表上（随机生成，因为它们取决于用户输入），我想看看我会遇到多少不同的毫秒值：

     SELECT COUNT(*) FROM
 (
   SELECT DISTINCT y.millis FROM
   (
      SELECT RIGHT(RTRIM(LTRIM(x.submissionDate)), 6) AS millis FROM (
         SELECT convert(char(32), submission_date, 139) AS submissionDate FROM someTable
      ) x
   ) y
 ) z

    上面查询中最里面的 SQL 语句也用于我的问题，并产生我在问题中发布的值。

    复合查询结果为237，与上述一致。

    此外，SQL Server 文档（因为datetime实现是 Sybase ASE 和 SQL Server 之间共享代码库的一部分而适用）有以下说明：

    图片如下：

    在此处输入图像描述

    在同一页面中还指出：

    datetime 不符合 ANSI 或 ISO 8601。

    总的来说，我认为使用这种毛茸茸的类型是一种反模式。我无法理解为什么有人不会简单地使用 anINT来保存 Epoch 以来的秒数（是的，我知道大约 2038 年），或者 aBIGINT来保存 Epoch 以来的毫秒甚至微秒或纳秒。

    详尽测试后更新

    我能够找到一些时间对此事进行详尽的测试并设法查明真相。这是我的发现（针对 Sybase ASE 15.7 进行了测试，我猜同样的结果也适用于 SQL Server）：

    我已经通过实验证明，服务器Datetime以 1/300 秒的分辨率保持客户端提供给它的值。我使用以下 DDL 创建了一个表：

    DROP TABLE DatetimeTest;
CREATE TABLE DatetimeTest (
    d DATETIME NOT NULL
);

    …并用 1000 个值填充它（使用 Java 中的驱动程序），毫秒增量为 1/1000 秒。执行以下查询：

     SELECT COUNT(*) FROM
 (
 SELECT DISTINCT y.millis FROM
 (
 SELECT RIGHT(RTRIM(LTRIM(x.d)), 6) AS millis FROM (
 SELECT convert(char(32), d, 139) AS d FROM DatetimeTest
 ) x
 ) y
 ) z

    ...... 准确地产生了300。

    以上确定服务器只能为小数秒部分保存 300 个不同的值。这并不能证明保留的实际值是 1/300 秒的倍数，但这是一个合理的指示。

    以上暗示的是客户端提供给服务器的任何小数秒存储都可能有损失。因此，我做了一些进一步的测试来确定究竟有多有损。我发现如下：

    • 客户端中具有秒精度的时间值可以无损地存储在服务器中（好吧，对此毫无疑问）
    • 客户端中精度为十分之一秒的时间值可以无损地存储在服务器中
    • 客户端中精度为百分之一秒的时间值可以无损地存储在服务器中
    • 客户端中只有 10% 的毫秒精度的时间值可以无损地存储在服务器中（对应于整百分之一秒的时间值）。对于其余 90% 的值，可以观察到客户端提供的值与服务器存储的值之间的差异（包括 3 毫秒）。

    以上与服务器将时间分量存储为 1/300 秒的整数的假设一致：

    • 任何十分之一秒的值都可以精确地表示为以下形式的分数n/300
    • 任何百分之一秒的值都可以精确地 表示为以下形式的分数n/300
    • 只有 10% 的可能毫秒值可以精确地表示为形式中的分数n/300。

    最后，很明显，convert作为查询的函数有问题：

    SELECT convert(char(32), d, 139) AS d FROM DatetimeTest

    … 报告小数点后第三位为零，这显然不正确且令人困惑。但我不相信它反驳了上述观点。

    更新二

    奇怪的是，仅当我在测试中使用Sybase jConnect JDBC 驱动程序时，我的测试代码报告的毫秒值的非精确服务器映射的百分比为 90% 。对于开源 JTDS JDBC 驱动程序，应用了改进的 70% 左右。基本上，测试逻辑是我在客户端创建一个毫秒精度的值，将其存储在服务器中，然后从客户端再次读取它以查看我得到了什么。这种测试安排允许客户端驱动程序在返回途中（当客户端代码从数据库读取时）温和地纠正一些服务器值。

    显然，JTDS 驱动程序可以更巧妙地解释 Sybase 服务器存储小数秒组件的特殊方式，并且可以为所有300 个不同的服务器值这样做（补偿反向的舍入误差 - 从服务器到客户端 -必要时），而不仅仅是恰好对应于 0-1000 范围内的某个毫秒值的 100 个。

    无论如何，这不会改变服务器端的事实，并且只有当一个人可以控制客户端语言和驱动程序时才相关，这些语言和驱动程序将用于Datetime从服务器中提取值。

    我想我会跳过这部分以保持简单，但后来我想我可能会为了完整性而添加它。它还证实了服务器存储Datetime值的模型，因为所有这些数字（300、90%、70%）都符合叙述。但是生命太短暂了，是时候继续前进了。

    • 4

相关问题