0

I have a timestamp with timezone column in postgressql, named created , that the data are saved in UTC.

I am using pg-promise 8.4.4 and I want to select the data, but set a different timezone for every client.

The logic is that my angular app uses the node.js endpoints as an api and I guess I can set the timezone while creating a DB connection, but that would be good just for my app.

What if another app, from another timezone, wants to hit the same node endpoint? How can they set the timezone?

So, in SQL I can do something like

set timezone to 'europe/athens';
select created from table;

But, how do I do this in pg-promise, for different clients?

I could do something like 

cmsuser.generalQuery(`select firstname, created AT TIME ZONE '+02' from table WHERE AND activated = $1 ORDER BY created`, [true])
  .then((resolved)=>{
    res.json({success:true, activated:resolved});
  })

and make the +02 part dynamic?

The cmsuser.generalQuery is 

const generalQuery = (text, params) => {
  const s = pgp.as.format(text, params);
  return db.any(s)
}

There are two problems with this approach right now

-1- With the above query, without having the timezone part as dynamic, I get the firstname, but not the created, that is the date. No errors in the console.

-2- Setting the timezone per query is not a smart thing to do.

What can I do here?

Thank you

slevin
  • 4,166
  • 20
  • 69
  • 129
  • Do you intend to keep setting a new timezone for every query? This seems terribly inefficient. Try to rephrase the question, so the intent makes better sense. Then it will be possible to advise you better. – vitaly-t Jul 08 '18 at 17:00
  • @vitaly-t Hi, I just edited the question, explaining my overall problem. You are right ,my approach is terribly inefficient, so maybe my overall problem can help you advice me. Thanks – slevin Jul 08 '18 at 17:15
  • You are looking at the whole thing in the wrong way. When a client makes a request, every time stamp it attaches is supposed to contain the client's timezone, so you can use date/time directly. You do not change it in the database or the connection, this will never work :) So your issue is not even related to `pg-promise`, it is a misunderstanding of how to write a server for multi-timezone support. – vitaly-t Jul 08 '18 at 18:14
  • I am not sure if I got this. When I query, the timestamp I get in the app, is the same as the one in the DB. And in the DB, is UTC. I even set all timezones in `postgresql.conf ` as UTC. So, this is a matter of node.js session timezone now? So, I should search for "node session timezone" or "node multi-timezone support"? Thanks – slevin Jul 08 '18 at 19:44

0 Answers0