javascript+Postgres:时区和时间戳的使用

Question

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

a.我是否应该使publishdate和createdate具有时区并设置UTC的时间戳？

b.用户post字符串，然后将其转换为utc时间戳，如下所示使用MomentJS并存储，当某人选择这一行时，将其显示为用户客户端时间反向使用MomentJS

c.我使用current_timestamp到createdate，current_timestamp是不是意味着服务器时间？我做得对吗？

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

a.我的数据库（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.用户将post提交到存储（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.将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 ($1,$2,$3,CURRENT_TIMESTAMP) RETURNING "ArticleId"';
  dbClient.query(query,[publishDate,active,createByUserId], function(error, result) {
    if (error) {
      reject(error);
    } else {
      fulfill(result);
    }
  });
});
};

更新
当我更改不带时区的所有列时，执行insertarticle显示错误

{[error:日期/时间字段值超出范围：“1439717298”]名称：“error”,长度：158,严重性：“error”,代码：“22008”,详细信息：未定义,提示：“可能需要不同的”datestyle“设置.”,位置：未定义,InternalPosition：未定义,InternalQuery：未定义,Where：未定义,架构：未定义,表：未定义,列：未定义,数据类型：未定义,约束：未定义,文件：“datetime.c”,行：“3775”,例程：“datetimeParseError”}

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

Answer 1

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

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

Answer 2

最简单的方法是始终存储不带时区和以UTC形式存储的时间戳。这样就可以很容易地使用它们进行显示和计算。差异只是一个减法，直接进行比较。

如果列是带有时区的时间戳，那么输入将被转换为UTC，但输出将处于当前设置的时区中，如果设置不正确，则可能会显示错误的值。这也降低了数据库的效率。

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

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

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