即使dateStrings：true，KNEX也会在服务器响应中向时间戳添加额外的信息 [英] KNEX adds extra info to timestamp in server response even though dateStrings: true

问题描述

我正在使用KNEX，Node / Express，MSSQL（TSQL）和数据表/编辑器库。

I'm using KNEX, Node/Express, MSSQL (TSQL), and the DataTables/Editor Libraries.

我不了解我的时间戳发生了什么。我需要返回一个DATE和一个TIME，但是同时又添加了额外的信息。

I don't understand what is happening to my timestamps. I need to return a DATE and a TIME, but extra information is being added to both.

我正在使用Node中的KNEX查询MSSQL视图：

I'm querying a MSSQL View with KNEX in Node:

    db.select().table('vueMySuperCoolView')
        .where({ StartDate: date })
        .orderByRaw('StartTime, LogDate')
        .then(data => res.json({ data }))  //responds with named array of objects data:[{}]
        .catch(err => console.log(err));  //more robust error output options exist

以下是一些构造视图的逻辑：

Here is some of the logic that constructs the view:

    select
        cast(t.StartTime as date) as StartDate
        ,cast(t.StartTime as time) as StartTime

这将在我可以使用的MS SQL Management Studio中产生可接受的输出：

This produces acceptable output in the MS SQL Management Studio that I can work with:

StartDate   StartTime
2020-05-21  09:30:00.0000000
2020-05-21  10:00:00.0000000
2020-05-21  10:30:00.0000000
...

但是，在服务器响应中，我的日期和时间如下：

            "StartDate": "2020-05-22T00:00:00.000Z",
            "StartTime": "1970-01-01T13:30:00.000Z",

在支持文章中，建议使用 dateString

In a support article, it was recommended that the "dateString" option be set to true, and it is.

connection: {
    user: '',
    password: '',
    database: 'DB',
    host: 'MSSQL',
    dateStrings: true,
    options: {
           instanceName: 'I'
       }
}

您能指出我正确的方向吗？

推荐答案

如果我的回答比我的要好，我会很乐意接受别人的回答。

I will happily accept another person's answer if it is better than mine.

我唯一的办法知道如何解决此问题的方法是将日期强制转换/转换为 varchar 。

The only way I know how to work around this issue is by casting/converting the dates to a varchar.

    select
        convert(varchar, cast(t.StartTime as date))  as StartDate
        ,convert(varchar, cast(t.StartTime as time), 120) as StartTime  -- grouping value

转换/转换后，日期和时间明确为 varchar ，不包含任何附加的垃圾。

After casting/converting, the date and time is explicitly a varchar, without any extra garbage attached to it. It's available in the frontend for further manipulation (i.e.: with moment.js).

另一件事可能很重要……考虑设置 options.useUTC （返回日期/时间数据时不要让KNEX / JS / NODE操作时区）：

Another thing that might be important... consider setting options.useUTC (do not let KNEX/JS/NODE manipulate the time zone when returning date/time data):

        options: {
            instanceName: 'MyInstance',
            // A boolean determining whether or not use UTC time for values without time zone offset (default: true).
            useUTC: false
        }

https://tediousjs.github.io/node-mssql/

这篇关于即使dateStrings：true，KNEX也会在服务器响应中向时间戳添加额外的信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！