javascript + postgres：时区和时间戳使用

javascript + postgres: timezone and timestamp usage

提问by user1775888

I'm not quite understanding the timestamp usage,
e.g.
User create article and they can choose PublishDate, the system also store CreateDateautomatically.

我不太了解时间戳的用法，
例如
用户创建文章，他们可以选择PublishDate，系统也会CreateDate自动存储。

a. Should I make PublishDateand CreateDatetimestamp with time zone and set utc?

一种。我应该用时区制作PublishDate和CreateDate时间戳并设置UTC吗？

b. User post string and then I convert like below use momentjsto utc timestamp and store, when someone select this row , show them as user client time reverse use momentjs

湾 用户发布字符串，然后我像下面一样转换momentjs为 utc 时间戳并存储，当有人选择这一行时，将它们显示为用户客户端时间反向使用momentjs

c. I use CURRENT_TIMESTAMPto the CreateDate, the CURRENT_TIMESTAMPdoes that mean the server time? am I doing correct?

C。我习惯CURRENT_TIMESTAMP了CreateDate，这CURRENT_TIMESTAMP是否意味着服务器时间？我做得对吗？

My thinking is I always insert utc timezone timestamp to the database, and wherever the place user/client read, convert the data to user/client timezone? am I doing correct?

我的想法是我总是将 utc 时区时间戳插入数据库，并且无论用户/客户端读取的位置在哪里，都将数据转换为用户/客户端时区？我做得对吗？

a. my database(postgres) created by

一种。我的数据库（postgres）由

CREATE TABLE IF NOT EXISTS "Article"(
"ArticleId" SERIAL NOT NULL,
"PublishDate" timestamp with time zone,
"Active" bit NOT NULL,
"CreateByUserId" integer,
"CreateDate" timestamp with time zone,
PRIMARY KEY ("ArticleId")
);

SET timezone = 'UTC';

b. user submit post to store (nodejs)

湾 用户提交帖子到商店（nodejs）

// publishDate: '{"y":2015,"m":8,"d":16,"h":15,"mi":46,"s":24}
var publishDate = JSON.parse(req.body.publishDate); 

var leadingZeroAndDateFormat = function(publishDate) {
  return new Promise(function (fulfill, reject){
    if (publishDate.m < 10) { publishDate.m = '0'+publishDate.m; }
    if (publishDate.d < 10) { publishDate.d = '0'+publishDate.d; }
    if (publishDate.h < 10) { publishDate.h = '0'+publishDate.h; }
    if (publishDate.mi < 10) { publishDate.mi = '0'+publishDate.mi; }
    if (publishDate.s < 10) { publishDate.s = '0'+publishDate.s; }
    var str = publishDate.y+'-'+publishDate.m+'-'+publishDate.d+' '+publishDate.h+':'+publishDate.mi+':'+publishDate.s;
    var utc = moment(str).unix();
    fulfill(utc);
  });
};

c. insert to database the CreateDateuse CURRENT_TIMESTAMP

C。插入到数据库CreateDate使用CURRENT_TIMESTAMP

var insertArticle = function(publishDate, active, createByUserId) {
return new Promise(function (fulfill, reject){
  var query = 'INSERT INTO "Article" ("PublishDate","Active","CreateByUserId","CreateDate") VALUES (,,,CURRENT_TIMESTAMP) RETURNING "ArticleId"';
  dbClient.query(query,[publishDate,active,createByUserId], function(error, result) {
    if (error) {
      reject(error);
    } else {
      fulfill(result);
    }
  });
});
};

Update
When I change all column without timezone then I execute insertArticleshows the error

更新
当我更改没有时区的所有列然后我执行insertArticle显示错误

{ [error: date/time field value out of range: "1439717298"] name: 'error', length: 158, severity: 'ERROR', code: '22008', detail: undefined, hint: 'Perhaps you need a different "datestyle" setting.', position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'datetime.c', line: '3775', routine: 'DateTimeParseError' }

{ [error: date/time field value out of range: "1439717298"] name: 'error', length: 158, severity: 'ERROR', code: '22008', detail: undefined, hint: 'Perhaps you need a different "datestyle" setting.', position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'datetime.c', line: '3775', routine: 'DateTimeParseError' }

var insertArticle = function(publishDate, active, createByUserId) {
return new Promise(function (fulfill, reject){
  var query = 'INSERT INTO "Article" ("PublishDate","Active","CreateByUserId","CreateDate") VALUES (,,,) RETURNING "ArticleId"';
  dbClient.query(query,[publishDate,active,createByUserId,moment.utc().unix()], function(error, result) {
    if (error) {
      reject(error);
    } else {
       fulfill(result);
    }
  });
});
};

回答by Sami Kuhmonen

The simplest way is to always store time stamps without time zone and in UTC. This way it is always easy to use them for display and calculations. A difference is just a subtraction and comparisons go directly.

最简单的方法是始终以 UTC 格式存储没有时区的时间戳。通过这种方式，可以很容易地将它们用于显示和计算。差异只是减法而直接进行比较。

If the column was a time stamp with time zone, then the input would be converted to UTC anyway, but the output would be in the currently set time zone and if it's not set properly it might show wrong values. It also makes the database less efficient.

如果该列是带有时区的时间戳，那么无论如何输入都将转换为 UTC，但输出将在当前设置的时区中，如果设置不正确，则可能会显示错误的值。它还降低了数据库的效率。

In the presentation layer the timestamps can be shown in the proper time zone and values input can also be converted to UTC for storage.

在表示层，时间戳可以显示在适当的时区中，输入的值也可以转换为 UTC 进行存储。

This is the simplest, most efficient and most flexible way of handling timestamps.

这是处理时间戳的最简单、最有效和最灵活的方式。

Using CURRENT_TIMESTAMP will get the timestamp from the server at the time of execution.

使用 CURRENT_TIMESTAMP 将在执行时从服务器获取时间戳。

回答by Danny Dulai

The incoming timestamps without timezone are parsed in local time instead of UTC -- I'd call this a bug in node-postgres. Anyway, you can override it to do the right thing by adding the following code:

没有时区的传入时间戳是在本地时间而不是 UTC 中解析的——我称之为 node-postgres 中的错误。无论如何，您可以通过添加以下代码来覆盖它以做正确的事情：

pg.types.setTypeParser(1114, function(stringValue) {
    console.log(stringValue);
    return new Date(Date.parse(stringValue + "+0000"));
})