Store UTC Dates in Postgres with JavaScript

Here, we'll look at a way to store UTC dates in Postgres with JavaScript.

UTC

It's all about the UTCs, baby. It's still not great. You might still get it wrong. But you're making things easier on yourself.

Column Type

You need a place to store the date. Postgres uses tables with columns. Usually, there's one piece of data per column. The columns are typed.

For UTC dates, use the column type TIMESTAMP WITHOUT TIME ZONE (aka, just TIMESTAMP) (as opposed to TIMESTAMPTZ).

By doing this, Postgres will not adjust your data at all as it is inserted or selected from the table. It will save it just as it comes in. It'll come out the same way. UTC all the way down.

Here's a clarifying explanation of the difference in the column types on StackOverflow.

Create the Table

=># create table test(date timestamp without time zone);
CREATE TABLE
=># \d
                List of relations
 Schema |        Name         |   Type   | Owner
--------+---------------------+----------+-------
 public | test                | table    | jt
(1 rows)

=># \d test
                          Table "public.test"
 Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
 date   | timestamp without time zone |           |          |

Create a Date in the Database

You can generate "now" in Postgres:

=># insert into test (date) values (CURRENT_TIMESTAMP);
INSERT 0 1
=># select * from test;
            date
----------------------------
 2023-04-11 22:18:21.614613
(1 row)

Query the Date

When the date column is queried with a library like node-postgres, the Date will be instantiated with a UTC ISO string.

import * as dotenv from "dotenv";
import pg from "pg";

dotenv.config();

const pool = new pg.Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  connectionTimeoutMillis: 5000,
});
const db = await pool.connect();

const res = await db.query("select * from test2");

console.log(res.rows[0].data.toISOString());

And sure enough, it's UTC (see that 'Z' on the end):

2023-04-12T04:18:21.614Z

Insert UTC Date from JavaScript

And what if we change the code to insert a date instead of generating a date from the database?

Local Date

Insert a new Date, implicitly of the timezone of the runtime:

const res = await db.query({
  text: "insert into test (date) values ($1) returning *",
  values: [new Date()],
});

It is automatically converted from that local date to a UTC date by node-postgres. Queried and logged in JS, it is:

2023-04-11T22:55:24.773Z

That works.

UTC Date

Insert a new UTC Date, made so by using a propery ISO string ending 'Z' when instantiating the date:

const res = await db.query({
  text: "insert into test (date) values ($1) returning *",
  values: [new Date("2023-04-11T23:00:00.000Z")],
});

It passes the UTC Date straight into Postgres as-is. Queried and logged in JS, it is:

2023-04-11T23:00:00.000Z

It works.

UTC ISO String

Instead of a Date, does the ISO string work (no instantiation):

const res = await db.query({
  text: "insert into test (date) values ($1) returning *",
  values: ["2023-04-11T23:33:00.000Z"],
});

Queried and logged:

2023-04-12T05:33:00.000Z

It looks like UTC. And it is. But it has been adjusted by node-postgres, and not in a good way. The runtime timezone is MDT (-06:00), so the "UTC" time that it has stored has attempted to add 6 hrs and zero out the offset, going from 23:33 on the 11th to 5:33 on the 12th. This doesn't work. Instantiate Dates instead.

What else? Any other gotchas we should document while using JavaScript and Postgres to store dates?