javascript + postgres: timezone and timestamp usage

Question

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

a. Should I make PublishDate and CreateDate timestamp with time zone and set utc?

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

c. I use CURRENT_TIMESTAMP to the CreateDate, the CURRENT_TIMESTAMP does that mean the server time? am I doing correct?

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?

a. my database(postgres) created by

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)

// 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 CreateDate use 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);
    }
  });
});
};

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

{ [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 ($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);
    }
  });
});
};

Use always UTC times and time stamp without time zone, since there is no need to store different time zones. Then in the presentation layer handle the zones if you need to. — Sami Kuhmonen
Thanks for reply, does that mean make the column set without timezone? then when insert use momentjs utc timestamp — user1775888
Yes, make the column in the database without time zone and always store UTC times from moment. This way you always know what the data is and can handle it easily. Presentation can be done in any time zone you choose easily also. — Sami Kuhmonen
A) Always use type timestamptz. B) Always insert as UTC, which is the format that PostgreSQL understands automatically always. If you use pg-promise, you get UTC always by default. — vitaly-t

Sami Kuhmonen Sami Kuhmonen · Accepted Answer · 2015-08-16T09:09:40

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.

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.

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.

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.

javascript + postgres: timezone and timestamp usage

2 Answers