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);
    }
  });
});
};

Answer 1

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.