Difference between date, timestamp with timezone and timestamp without timezone in Postgres

I created a table in Postgres with 3 columns of these types:

• date
• timestamp without time zone
• timestamp with time zone

CREATE TABLE dates_test (
 date_value date,
 timestamp_without_tz_value timestamp without time zone,
 timestamp_with_tz_value timestamp with time zone
);

Goal #1: Detect if time zone which was set in Node.js environment at the moment of insert, affects value in the database, when we insert YYYY-MM-DD. Answer: NO. Anyway in a result we have UTC date in the database.

I set process.env.TZ to Europe/Kiev and inserted 2021–07–01 into this table:

INSERT INTO dates_test (date_value, timestamp_without_tz_value, timestamp_with_tz_value) VALUES ('2021-07-01','2021-07-01','2021-07-01')

I set process.env.TZ to UTC and inserted 2021–07–02 into this table:

INSERT INTO dates_test (date_value, timestamp_without_tz_value, timestamp_with_tz_value) VALUES ('2021-07-02','2021-07-02','2021-07-02')

As a result, this is what I see in PgAdmin. Both dates are in UTC:

Let’s check in which timezone PgAdmin is working. It’s UTC.

Test #1 summary: So, in both cases we inserted a UTC date, even if Node.js environment was set to some other timezone. It’s reasonable.

Goal #2: Detect how Node.js environment timezone (process.env.TZ) affects values which we get when query database.

1. I set process.env.TZ set to Europe/Kiev. And select values which I inserted before — select * from dates_test. This is what I get:

[
    {
        "date_value": "2021-06-30T21:00:00.000Z",
        "timestamp_without_tz_value": "2021-06-30T21:00:00.000Z",
        "timestamp_with_tz_value": "2021-07-01T00:00:00.000Z"
    },
    {
        "date_value": "2021-07-01T21:00:00.000Z",
        "timestamp_without_tz_value": "2021-07-01T21:00:00.000Z",
        "timestamp_with_tz_value": "2021-07-02T00:00:00.000Z"
    }
]

2. I set process.env.TZ set to UTC. And select values which I inserted before — select * from dates_test. This is what I get:

[
    {
        "date_value": "2021-07-01T00:00:00.000Z",
        "timestamp_without_tz_value": "2021-07-01T00:00:00.000Z",
        "timestamp_with_tz_value": "2021-07-01T00:00:00.000Z"
    },
    {
        "date_value": "2021-07-02T00:00:00.000Z",
        "timestamp_without_tz_value": "2021-07-02T00:00:00.000Z",
        "timestamp_with_tz_value": "2021-07-02T00:00:00.000Z"
    }
]

3. I set process.env.TZ set to Ametics/Los_Angeles. And select values which I inserted before — select * from dates_test. This is what I get:

[
    {
        "date_value": "2021-07-01T07:00:00.000Z",
        "timestamp_without_tz_value": "2021-07-01T07:00:00.000Z",
        "timestamp_with_tz_value": "2021-07-01T00:00:00.000Z"
    },
    {
        "date_value": "2021-07-02T07:00:00.000Z",
        "timestamp_without_tz_value": "2021-07-02T07:00:00.000Z",
        "timestamp_with_tz_value": "2021-07-02T00:00:00.000Z"
    }
]

Test #2 summary:

• date and timestamp_without_tz column values are treated as “dates in client’s timezone” and 2021–07–01 is returned as `2021–06–30T21:00:00.000Z` if I am in Kiev and as `2021–07–01T07:00:00.000Z` if I am in America/Los_Angeles
• So, Node.js environment timezone affects values which you get from date and timestamp_without_tzcolumns, and it does not affect values which you get from timestamp_with_tzcolumn because that column “remembers” that value is in UTC
• So, it’s better to use timestamp_with_tzif you want to get value in the same timezone in which it was saved (UTC) in ANY Node.js environment.