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

    }

  });

});

};