Fragmented Thought
TwitterGitHub

How to get IOS 8601 standard time strings out of Postgres using the pg package

By

Published:

Lance Gliser

Heads up! This content is more than six months old. Take some time to verify everything still works as expected.

Before I describe any of what this absurdly simple, yet required solution, I want to give a shout out to the folks that maintain the pg package. It's great, and I thank them. The only reason my work can be so simple is because they left hooks for just this kind of situation.

Thank you to you all.

Why would you to change the package return at all?

To be frank, my problem is far from everyone's. The pg package provides a pseudo object out of the box when dealing with timestamp or date columns that can handle most situations. There's two reasons it might not work for you:

  • It's not a 'real' Date object because it lacks a timezone (based on your column specification).
  • It's not a string, and you're a glutton for punishment. 🙌🏼

My specific silly use case is that I currently support two database drivers for the same application:

  • DynamoDB - Documents. The wonderful world of garbage in, garbage out exactly as I asked that I love.
  • Postgres - Relational. Some clients just gotta have it. 🤷🏻

That means the returns from either database model need to be same. Woo interface abstraction layers and database specific drivers. Every good language has them, so it's not a huge lift. Then you just instantiate both databases with docker compose in the validation pipeline and loop a set of instnatiated driver specific implementations in tests against them... Oh, and in the AWS method your pipeline can run the migration npm script but deploying to Kubernetes you need to run migrations of SQL files via Flyway via initContainer and secrets... 🤬

That my friends is an entirely different blog post, about what not to do even if it is possible.

Long rant short: Doesn't matter who is asking createdAt needs to be IOS 8601.

PG Type parsers

The solution to either of the reasons you might change the return is the concept of Parsers.

Note the official docs are empty, and return nothing on search. See instead: GitHub's README.md, which pretty good! Here's hoping a little more keyword bloat pointed at the right place helps them out!

Generally what you'll want to do is override how a specific data-type is parsed and turned into a JavaScript type.

In their simple example:

var types = require("pg").types;
types.setTypeParser(20, function (val) {
  return parseInt(val, 10);
});

They actually have some prewired handlers for most types, be sure to check the pg.types.builtins for your use case. In my case, pg.types.builtins.TIMESTAMP routes to parseTimestamp with a little magic calls const parseTimestampTz = require('postgres-date'). That won't do though, I demand strings!

ISO 8601 parser

Here's what I came up with:

import pg from "pg";

export const timestampWithoutValuesFormatter = (timestamp: string): string => {
  const [date, time] = timestamp.replace(/Z$/, "").split(" ");
  const [year, month, day] = date.split("-");
  const [hour, minute, combinedSeconds] = time.split(":");
  const [seconds, fractionalSeconds] = combinedSeconds.split(".");
  const fractionalSecondsInt = parseInt(fractionalSeconds);
  const fractionalSecondsFloat = fractionalSeconds
    ? fractionalSecondsInt / 10 ** fractionalSeconds.length
    : 0;

  return [
    [year, month, day].join("-"),
    "T",
    [hour, minute, seconds].join(":"),
    ".",
    fractionalSecondsFloat.toFixed(3).split(".").at(1),
    "Z",
  ].join("");
};
pg.types.setTypeParser(
  pg.types.builtins.TIMESTAMP,
  timestampWithoutValuesFormatter
);

And some tests, based on edge cases I noticed:

import { timestampWithoutValuesFormatter } from "./postgres";

describe("config postgres", () => {
  describe("timestampWithoutValuesFormatter", () => {
    it("should ensure all values end with 'Z'", () => {
      // Note the lack of ending 'Z'
      expect(timestampWithoutValuesFormatter("2023-03-30 21:03:15.563")).toBe(
        "2023-03-30T21:03:15.563Z"
      );
    });

    it("should include a 'T' separator for time src", () => {
      // Note the empty space between date and time
      expect(timestampWithoutValuesFormatter("2023-03-30 21:03:15.562")).toBe(
        "2023-03-30T21:03:15.562Z"
      );
    });

    it("should provide at least 3 digits of fraction second precisions", () => {
      // (Note the lack of ending 3rd 0 and Z
      expect(timestampWithoutValuesFormatter("2023-03-30 21:03:15.56")).toBe(
        "2023-03-30T21:03:15.560Z"
      );
    });
  });
});

Should this have taken an hour to figure out? Probably not.
Was it worth another half hour to write? If it helps even one of you, yes. 🎉

Cheers!