17735_zh-CN

liliconcom

带时间戳/日期时间/日期数据的 MS SQL 查询

大家好，

我们想要将数据表保存到数据库中，然后查询数据库（使用 MS SQL）来检索该表。 由于我们在将时间戳传输到数据库时遇到一些问题，因此我们设置了一个解决方法。 首先，JMP 中的表有一个名为“SecondsSince1904”的“日期”列，其中日期时间存储为自 1904 年纪元 (01.01.1904 00:00:00) 以来经过的秒数。 该列的格式为“固定小数点 (30,0)”。 为了将数据表传输到数据库，我们使用以下脚本：

 

 

Names Default To Here(1);
dt = Current Data Table();
// This will delete the table "yourTable" in the database. BE CAREFUL!!!
Open Database(
    "yourDSNconnectionString",
    "DROP TABLE dbo.yourTable"  
);
// This will upload the table "dt = Current Data Table" to the database, where it will be saved as "yourTable".
dt << Save Database("yourDSNconnectionString",
    "dbo.yourTable"
);
// After saving the table to the database, we find the column "SecondsSince1904" formatted as FLOAT.
// Now we want to manipulate the table "yourTable" and add a computed column named "timestamp", where we will display the correct timestamp,
// i. e. something like "2015-09-26 00:00:02.000".
// We do this by calculating the correct timestamp from the column "SecondsSince1904". We also add an additional column named "SecondsAfterMidnight" just for fun.
dbc = Create Database Connection(
    "yourDSNconnectionString"
);
Execute SQL(dbc,
    "
    ALTER TABLE dbo.yourTable ADD timestamp AS
    DATEADD(ss, (CAST(yourTable.SecondsSince1904 AS BIGINT)%86400),
    DATEADD(dd, (CAST(yourTable.SecondsSince1904 AS BIGINT)/86400), '1904-01-01' ) );
    ALTER TABLE dbo.yourTable ADD SecondsAfterMidnight AS
    (CAST(yourTable.SecondsSince1904 AS BIGINT)%86400)
    "
);
Close Database Connection(dbc);

 

 

到目前为止，一切都很好。 到目前为止没有问题。 “SecondsAfter1904”已正确传输到数据库，并且时间戳也已正确计算。 数据库里看一下：

现在，我们通过转至文件 > 数据库 > 打开表来查询数据库并将表检索回 JMP。 这就是事情变得混乱的地方。 看看JMP：

时间戳已损坏。 发生了一些错误。 也许是位移位或类似的东西。 为了得出结论，我“按位”增加了 SecondsSince1904 。

 

有谁知道这里发生了什么？ 这是一个错误吗？ 任何帮助表示赞赏。

 

干杯，
胜利者

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

谢谢你。 我将使用 TRUNCATE 命令尝试您的建议并插入现有表。 但我目前不知道如何正确指定应从 JMP 中的哪个数据表中获取数据。 也许您的有益贡献这个线程将引导我找到解决方案......

但是，我能够通过使用数据库中的 datetime2(0) 解决原始问题，如下所述。

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询

所以，最后发现，当查询开始时，数据库内一定存在转换问题。 我尝试解释一下：

假设一些时间戳以“日期时间”格式存储在数据库中。 当我运行查询时，时间戳在传输时变得错误，如上面的示例所示。 当时间戳以“datetime2”格式（默认为“datetime2(7)”）存储时，也会发生同样的情况。

但是，如果我将数据库中的一列从“datetime2(7)”转换为“smalldatetime”，我犯了同样的错误。 因此我推测问题不在 JMP 内部，而是在数据库内部。 我认为，JMP 在下载它们之前会向带有时间戳的列发送一些 CAST 或 CONVERT 命令。 我想这就是问题发生的时刻。

 

为了克服这个问题，可以将带有时间戳的列转换为“日期时间2(0) “ 格式。 这是 JMP 正确读取的格式。

这是完整的代码：

 

Names Default To Here(1);
dt = Current Data Table();
dt << Save Database("yourDSNconnectionString",
    "dbo.yourTable"
);
dbc = Create Database Connection(
    "yourDSNconnectionString"
);
Execute SQL(dbc, // Here we calculate the correct timestamp/date from the Macintosh or JMP epoche (01.01.1904 00:00:00)
    "
    ALTER TABLE dbo.yourTable ADD timestamp AS
    DATEADD(ss, (CAST(yourTable.SecondsSince1904 AS BIGINT)%86400),
    DATEADD(dd, (CAST(yourTable.SecondsSince1904 AS BIGINT)/86400), '1904-01-01 00:00:00' )  );
    "
);
Execute SQL(dbc,
    "
    ALTER TABLE dbo.yourTable ADD Date datetime2(0);
    "
);
Execute SQL(dbc,
    "
    UPDATE dbo.yourTable
    SET Date = CONVERT(datetime2(0), timestamp);
    "
);
Execute SQL(dbc,
    "
    ALTER TABLE dbo.yourTable DROP COLUMN timestamp, SecondsSince1904
    "
);
Close Database Connection(dbc);

 

 

也许这对某人有帮助

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询

你想念明白了。将日期以日期时间格式保存在数据库中。PMroz 建议将所有内容都转换为日期字符串，然后再将其发送到不同的平台。在处理不同的时代时，这是一个方便的技巧。

您在从 JMP 到 SQL Server 的过程中遇到问题，因为您删除了数据表。您应该使用“TRUNCATE TABLE table_name”清除它。JMP 中没有日期/时间列，它们只是格式为日期时间的数字列。因此，当您使用 << Save Database 时，JMP 仅创建一个数字列，因为它不考虑时间。但是，如果您已经有一个 SQL 表，其中有一列格式化为日期时间并插入其中，那么应该没问题。

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询

虽然是猜测，但我认为你是对的。 真的好像每一秒都增加了 1000 秒。 此外，重置发生在 23 到 123 秒之间。 我可以想象，问题在于 SQL 中的日期时间格式具有毫秒、微秒和纳秒 (dd.mm.yyyy hh:mm:ss.mmmmmmn)。 继续猜测，可能是在从数据库导入到 JMP 时，JMP 认为 1000 毫秒是 1000 秒，而不是 1 秒。

我尝试过几种格式和样式，但无法教 JMP 如何正确从数据库导入表格。 这是我的最新代码，它确保数据库中的时间戳格式与 JMP 中的相同。 该格式称为“smalldatetime”，看起来像“dd.mm.yyyy hh:mm:ss”。

...

执行SQL(dbc,

”

ALTER TABLE dbo.berstTry01 添加时间戳 AS

DATEADD(ss, (CAST(berstTry01.SecondsSince1904 AS BIGINT)%86400), CAST(

DATEADD(dd, (CAST(berstTry01.SecondsSince1904 AS BIGINT)/86400), '1904-01-01 00:00:00' ) ASsmalldatetime) ) ;

更改表 dbo.berstTry01 添加 SecondsAfterMidnight AS

(CAST(berstTry01.SecondsSince1904 AS BIGINT)%86400);

”

）；

...

不幸的是这也不起作用。 现在我不知道该怎么做才能让它发挥作用

数据库中应用的公式实际上根本不与JMP交互。 而且计算显然是正确的，从第一张图就可以推断出来。 发生的情况是，一个 INTEGER 被传输到数据库，并且 INTEGER 的值恰好是自“JMP 纪元”以来经过的秒数（自 1904 年以来的秒数）。

如果我运行你的示例，我会得到正确的日期，但时间错误（18:07:17 而不是 14:14:14）...

我对这个问题调查得越多，我就越觉得这实际上是一个错误。 我正在运行 JMP 12.2.0。

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询

好吧，按照您的建议进行操作（将数据库中的日期作为字符串）是我考虑的选项之一。 但我认为以日期时间格式存储数据库中的日期会很方便。 但是，如果我将 JMP 表保存到数据库，日期将存储为 char 格式的字符串。 所以我不需要从日期时间转换为字符，我不知道为什么默认情况下日期不以日期时间格式存储在数据库中。 这就是为什么我写了上面的脚本......

我想我最终也会得到你的解决方案，即将数据库中的日期作为字符格式的字符串。 但这让我有点失望。

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询

这是推测，但看起来每一秒都增加了 1000 秒。然后 86.4 秒后重置为 0。这似乎是公式在拉动时与 JMP 交互的方式存在问题。

插入 SQL 时是否使用 JMP 纪元？SQL 是 1-1-1900。可能是您无法正确插入 SQL 的原因。

差异=编号( “1904 年 1 月 1 日” )--编号( “1900年1月1日” ）；

时间=（编号( “1914 年 7 月 14 日 14:14:14” ) + 差异)/ 86400 ;

//在SQL中

分贝=创建数据库连接( “驱动程序=SQL SERVER;服务器=BLAH\SQLEXPRESS;数据库=Test_DB” ）；

执行SQL （D b，

“选择演员表（” ||字符（时间）|| “作为日期时间）作为‘时间戳’”

）；

关闭数据库连接（D b）;

这为我返回了正确的值。 

回复：带有时间戳/日期时间/日期数据的 MS SQL 查询

从数据库读取数据时，我对日期/日期时间采取了稍微不同的方法。我将它们转换为字符串，然后进入 JMP 后将其转换回日期或日期时间。这样我就知道它是正确的。我在用着到_char在甲骨文中。我看到 SQL Server 使用转变或者投掷做类似的事情。JMP 中的函数可以将字符串日期转换为 JMP 日期。

走另一条路（JMP 到 SQL Server）我不确定为什么你会遇到这个问题。SQL Server 中的默认日期格式是否值得研究？