Inserting now() at time zone 'utc' gives invalid results

[Romick2005]

Steps to reproduce the issue:

  create table demo (
    id serial primary key not null,
    name character varying(50) not null,
    created timestamp with time zone not null default (now() at time zone 'utc')
  );

If I run this query using pg pool.execute:

client.query("insert into demo (name) values ('Test demo value from pg node server');");

and then run query from any DB IDE (in my case it was DataGrip):

insert into demo (name) values ('Test demo value from DB IDE DataGrip');

I got two different values (in terms of timezone shift):

2024-06-05 11:41:58.890770 +00:00 and 2024-06-05 14:42:01.658439 +00:00

Node, postgres DB and DB IDE DataGrip is on the same machine. System time is 17:42 Ukraine UTC +3.

I would expect that when timezone is not explicitly provided I should get proper UTC value 2024-06-05 14:42:01.658439 +00:00.

PS. SELECT current_setting('TIMEZONE'); from IDE: 'UTC' and from node server: 'Europe/Kiev'. Is there any way to stick to the UTC timezone somehow?

[Romick2005]

Fixed by setting DB default timezone: ALTER DATABASE [database_name] SET TIMEZONE='UTC';

[charmander]

You want just DEFAULT now(). now() is already a timestamptz.

[Romick2005]

But why now() that already contains timezone differs from now() at time zone 'utc'? I was assuming that there might be some database connection configuration like useUTC: true, but I didn't find any. So it seems like same query provides different result, depending on db client time zone even when there are no timestamp or time zone explicitly defined in the query.

PS Because everywhere I saw was recommendation: Always use timestamp field with utc timezone or just some other timezone.

[charmander]

now() at time zone 'utc' converts the timestamptz to a plain timestamp (without time zone), which is then interpreted as being in the connection’s time zone whenever it’s cast back to timestamptz.

PS Because everywhere I saw was recommendation: Always use timestamp field with utc timezone or just some other timezone.

Well, that recommendation is backwards. You should almost always use timestamptz (which unambiguously represents a moment in time, the usual datetime complexities notwithstanding), and almost never timestamp (which doesn’t represent anything in particular without context and is easy to misuse by accident, as seen here).