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
– Sami Kuhmonen, Commented Aug 16, 2015 at 8:30
Thanks for reply, does that mean make the column set without timezone? then when insert use momentjs utc timestamp — user1775888
– user1775888, Commented Aug 16, 2015 at 8:58
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
– Sami Kuhmonen, Commented Aug 16, 2015 at 8:59
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
– vitaly-t, Commented Aug 18, 2015 at 21:06

Sami Kuhmonen · Accepted Answer · 2015-08-16 09:12:58Z

14

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.

edited Aug 16, 2015 at 9:12

answered Aug 16, 2015 at 9:09

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1775888 Over a year ago

I update the column to without time zone, but when I execute ` insertArticle` shows the error [error: date/time field value out of range: "1439715951000"]

Sami Kuhmonen Over a year ago

That seems to be a millisecond value, probably UNIX epoch? Then you need to insert timestamp '1970-1-1' + interval '12357 seconds' to achieve an actual timestamp. Of course the number converted to seconds.

user1775888 Over a year ago

insert ` timestamp '1970-1-1' + interval '12357 seconds'` ? you mean execute this on database sql

Sami Kuhmonen Over a year ago

Yes, instead of just sending the number, send that with the number of seconds if moment gives it out as such. I'm not familiar with moment/node.js so unfortunately can't give the best answer. I would think there was a way to bind a timestamp/datetime value directly also.

stackdave Over a year ago

that's work if sometimes my server is in europe or usa ? i'd like to have a right date, and dont' depends of server location

user206209 · Accepted Answer · 2015-10-27 02:47:50Z

10

The incoming timestamps without timezone are parsed in local time instead of UTC -- I'd call this a bug in node-postgres. Anyway, you can override it to do the right thing by adding the following code:

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

answered Oct 27, 2015 at 2:47

user206209

5 Comments

Vahid Alimohamadi Over a year ago

Thanks for answer. I saw this piece of code in seqlize.js docs too. But where i should write this function? get confused!

user206209 Over a year ago

just add it to the top of your node.js source file -- before you make pg.connect calls

Paul Grimshaw Over a year ago

Just a note for others, i wanted to achieve the same thing, but my date / time column was of type TIMESTAMP. Using the above code with a value of 1184 instead worked

Mike Finch Over a year ago

Using the node-postgres module version 8.11.2, I have observed that pg.types.builtins.TIMESTAMPTZ has a value of 1184, and pg.types.builtins.TIMESTAMP has a value of 1114. I got the idea to use pg.types.builtins constants from github.com/brianc/node-pg-types.

Mike Finch Over a year ago

Overriding the type parser for TIMESTAMP, and appending "+0000" to the given value, as @DannyDulai answered, worked for me. Thanks.

Collectives™ on Stack Overflow

javascript + postgres: timezone and timestamp usage

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related