postgresql to_char(date, 'day') get different result when using nodejs

Question

UPDATE 2

Using the suggestions from @Bergi, I added a db execution helper to set the timezone before executing the query. This allows me to control the timezone based on the logged in user's preferences.

async function execWithTZ(sql, params, timezone) {
  const client = await this.pool.connect();
  await client.query<any>(`SET TIMEZONE TO '${timezone}'`, []);
  return client.query<any>(sql, params);
}

const result = await execWithTZ('SELECT ...', [], 'Australia/Sydney');

QUESTION

I am trying to get Postgresql to return the day of the week of a specific date in a view. I later use this to display values by day.

The data is in a table with a column named updated which is a timestamptz field.

When running the following query using DBeaver SQL client against a PostgreSQL 12 database I get the values as I expect, with records updated on Thursday local time (just after midnight) showing up as Thursday

  SELECT 
        count(v.id) AS count,
        btrim(to_char(v.updated, 'DAY'::text)) AS day,
        date_trunc('DAY'::text, v.updated) AS "updatedDay"
 FROM table v
 GROUP BY (to_char(v.updated, 'DAY'::text)), (date_trunc('DAY'::text, v.updated))

I put this in a view and when I query this view using node-postgres the counts are for the previous day (Wednesday), which I presume is because the database thinks it should interpret the dates in the UTC timezone.

To further prove the point, when I change the above query to use DAYTZ instead of just DAY in DBeaver I get THURSDAYAEST but in nodejs as result I get WEDNESDAYUTC

Changing all my dates to be without timezone and forcing everything to UTC on the way in is not an option for me.

How can I make node-postgres tell the database what timezone I want these dates interpreted as so that I can get the correct day?

UPDATE 1

I managed to get PostgreSQL to return the correct values to postgres node by setting the database user's timezone.

ALTER ROLE visuo_ingest SET TIMEZONE TO 'Australia/Sydney';

Now the counts for things that happened on Thursday Sydney time is counted for Thursday and not Wednesday.

Still interested in a way to do this on the connection rather than the database user level.

I think it's Javascript which converts the SQL date string into Javascript date class which is UTC timezone. So, you may need to explicitly override the dates on how node-postgres translates the dates into Javascript objects, please refer github.com/brianc/node-postgres/issues/… — Govind
– Govind, Commented Aug 12, 2020 at 17:38
Thanks for your reply but the value WEDNESDAY comes back in a column from Postgreql. Postgreql is doing the date formatting and so conversion to string. — dvanrensburg
– dvanrensburg, Commented Aug 12, 2020 at 22:47

Bergi · Accepted Answer · 2020-08-13 01:30:20Z

1

Still interested in a way to do this on the connection rather than the database user level.

You already found the right setting, there's no reason to alter it on a role only. You can also change the setting in a client session by running the SET command

SET TIMEZONE TO 'Australia/Sydney';

Just put that in a pgClient.query("…"); right after connecting the client.

answered Aug 13, 2020 at 1:30

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

4 Comments

dvanrensburg Over a year ago

This works if I use a Postgresql client (such as DBeaver), but do you know how I can get the node postgres (node-postgres.com) library to do this? I tried pre-pending my query with SET TIMEZONE TO 'Australia/Sydney'; SELECT FROM ... but that fails with parsing error. I suppose I could try doing it using the connection string...

Bergi Over a year ago

@dvanrensburg That should work. Iirc you cannot use parameterised queries with multiple statements, but really just make two subsequent queries using the same client instance.

Bergi Over a year ago

@dvanrensburg What exactly is the error message you are getting?

dvanrensburg Over a year ago

Ah thanks Bergi, I'll try idea later today. I'll make a connection and then execute both queries in sequence without releasing it.

Collectives™ on Stack Overflow

postgresql to_char(date, 'day') get different result when using nodejs

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related