postgresql 使用 node-postgres 在 utc 中获取 Postgres“没有时区的时间戳”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20712291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use node-postgres to get Postgres "timestamp without timezone" in utc
提问by Isaac Dontje Lindell
I've got some timestamps stored as the Postgres type timestamp without time zone.
我有一些时间戳存储为 Postgres 类型timestamp without time zone。
I'll use the timestamp 2013-12-20 20:45:27as an example. I'm intending that this represent a UTC timestamp.
我将使用时间戳2013-12-20 20:45:27作为示例。我打算这代表一个UTC时间戳。
In psql, if I run the query SELECT start_time FROM table_name WHERE id = 1, I get back that timestamp string, as expected: 2013-12-20 20:45:27.
在psql里，如果我运行查询SELECT start_time FROM table_name WHERE id = 1，我找回那种时刻字符串，如期望的那样2013-12-20 20:45:27。
However, if in my Node application, I use the node-postgres library to run that same query, I get back a timestamp in the local timezone: Fri Dec 20 2013 20:45:27 GMT-0600 (CST). This is a Javascript date object, but it's already stored as that timezone. What I really want is a date object (or even just a string) that represents 2013-12-20 20:45:27 GMT+0000. I already knowthat this time is UTC.
但是，如果在我的 Node 应用程序中，我使用 node-postgres 库来运行相同的查询，我会返回本地时区的时间戳：Fri Dec 20 2013 20:45:27 GMT-0600 (CST). 这是一个 Javascript 日期对象，但它已经存储为该时区。我真正想要的是一个日期对象（甚至只是一个字符串），它代表2013-12-20 20:45:27 GMT+0000. 我已经知道这个时间是 UTC。
I've tried setting the timezone param in my postgresql.conf file to: timezone = 'UTC', with no difference in results.
我尝试将 postgresql.conf 文件中的时区参数设置为: timezone = 'UTC'，结果没有区别。
What am I doing wrong?
我究竟做错了什么？
EDIT
编辑
The issue seems to be in this file: https://github.com/brianc/node-postgres/blob/master/lib/types/textParsers.js
问题似乎出在这个文件中：https: //github.com/brianc/node-postgres/blob/master/lib/types/textParsers.js
If the date string returned from Postgres doesn't have a time-zone specified (i.e. Z, or +06:30, then it just constructs a JavaScript date object, which I believe will just include the local time zone. I either need to change my app to store time zones in the DB or override this converter.
如果从 Postgres 返回的日期字符串没有指定时区（即Z, or +06:30，那么它只是构造一个 JavaScript 日期对象，我相信它只会包含本地时区。我要么需要更改我的应用程序以存储DB 中的时区或覆盖此转换器。
回答by BadIdeaException
Not to revive an old question, but seeing how I just had the exact same issue here, there is an alternative solution that works by overriding the type parser to use for timestamp without time zone:
不是要恢复一个老问题，而是看到我在这里遇到了完全相同的问题，有一个替代解决方案可以通过覆盖类型解析器来使用timestamp without time zone：
var pg = require('pg');
var types = pg.types;
types.setTypeParser(1114, function(stringValue) {
return stringValue;
});
This will keep node-pg from parsing the value into a Dateobject and give you the raw timestamp string instead.
这将阻止 node-pg 将值解析为Date对象，而是为您提供原始时间戳字符串。
Source: Got it from node-postgres issues
回答by stone
You can modify the parser, as @BadIdeaException suggests. Following are more details on why it doesn't work as expected, and two possible solutions.
正如@BadIdeaException 建议的那样，您可以修改解析器。以下是有关为什么它无法按预期工作的更多详细信息，以及两种可能的解决方案。
For columns with type timestamp without time zone, the parser receives a string in ISO 8601 format, with no time zone specified: 2016-07-12 22:47:34
对于 type 的列timestamp without time zone，解析器接收一个 ISO 8601 格式的字符串，没有指定时区：2016-07-12 22:47:34
Any time you create a Date object in Javascript, if you do not specify a time zone, it assumes the date is in the current time zone. For UTC dates, which by definition are in the GMT time zone, this will give you a date with the incorrect absolute value (date.value), unless your Javascript happens to be running in the GMT time zone.
任何时候在 Javascript 中创建 Date 对象时，如果不指定时区，它都会假定日期在当前时区。对于 UTC 日期，根据定义在 GMT 时区，这将为您提供一个绝对值不正确的日期(date.value)，除非您的 Javascript 恰好在 GMT 时区运行。
Therefore, that ISO 8601 string can't be directly converted into a UTC date by the Date constructor. Your options are: Modify the string so that it will be interpreted as UTC:
因此，该 ISO 8601 字符串无法通过 Date 构造函数直接转换为 UTC 日期。您的选项是： 修改字符串，使其被解释为 UTC：
var pg = require('pg');
var types = pg.types;
types.setTypeParser(1114, function(stringValue) {
return new Date(stringValue + "+0000");
});
or let your date be created with the "wrong" (current) time zone, and then extract the values for it (still in your current time zone), and then use those values to generate a date in the UTC time zone. Note that Date.UTC() returns a date valuerather than an object, which can then be passed to the Date constructor.
或者让您的日期使用“错误”（当前）时区创建，然后为其提取值（仍在当前时区中），然后使用这些值生成 UTC 时区中的日期。请注意 Date.UTC() 返回一个日期值而不是一个对象，然后可以将其传递给 Date 构造函数。
types.setTypeParser(1114, function(stringValue) {
var temp = new Date(stringValue);
return new Date(Date.UTC(
temp.getFullYear(), temp.getMonth(), temp.getHours(), temp.getMinutes(), temp.getSeconds(), temp.getMilliseconds())
);
}
回答by Isaac Dontje Lindell
It's not the best solution, but I just switched to using the Postgres type timestamp with time zoneand made sure that all dates I persisted to the DB were in UTC.
这不是最好的解决方案，但我只是切换到使用 Postgres 类型timestamp with time zone并确保我保存到数据库的所有日期都是 UTC。
