title: javascript + postgres: timezone and timestamp usage
body: <p>I'm not quite understanding the timestamp usage,<br>
  e.g.<br>
  User create article and they can choose <code>PublishDate</code> , the system also store <code>CreateDate</code> automatically.   </p>
  
  <p>a. Should I make <code>PublishDate</code> and <code>CreateDate</code> timestamp with time zone and set utc?  </p>
  
  <p>b. User post string and then I convert like below use <code>momentjs</code> to utc timestamp and store,  when someone select this row , show them as user client time reverse use <code>momentjs</code> </p>
  
  <p>c. I use <code>CURRENT_TIMESTAMP</code> to the <code>CreateDate</code>, the <code>CURRENT_TIMESTAMP</code> does that mean the server time?  am I doing correct? </p>
  
  <p>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?  </p>
  
  <p>a.  my database(postgres) created by </p>
  
  <pre><code>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';
  </code></pre>
  
  <p>b. user submit post to store (nodejs)</p>
  
  <pre><code>// 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 &lt; 10) { publishDate.m = '0'+publishDate.m; }
      if (publishDate.d &lt; 10) { publishDate.d = '0'+publishDate.d; }
      if (publishDate.h &lt; 10) { publishDate.h = '0'+publishDate.h; }
      if (publishDate.mi &lt; 10) { publishDate.mi = '0'+publishDate.mi; }
      if (publishDate.s &lt; 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);
    });
  };
  </code></pre>
  
  <p>c. insert to database the <code>CreateDate</code> use <code>CURRENT_TIMESTAMP</code></p>
  
  <pre><code>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);
      }
    });
  });
  };
  </code></pre>
  
  <p><strong>Update</strong><br>
  When I change all column without timezone then I execute <code>insertArticle</code> shows the error  </p>
  
  <p><code>
  { [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' }
  </code></p>
  
  <pre><code>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);
      }
    });
  });
  };
  </code></pre>