0%
February 27, 2024

Javascript Compatible Timestamp in PostgreSQL

prisma

sql

Contents

The Result

  • Inside our table:

  • createdAt

    • It is js-compatible, which is used mostly in frontend;
    • It is also convenient to database for ORDER BY statements,
    • Sorting integers (by numerical value) is much easier than
      • sorting String's (by internal sorting rules of characters) or
      • sortingDatetime's (by internal data structure composing of year, month, timezone, etc).

  • createdAtHK is human-readable in HK timezone

Prisma

We add the following in any prisma model:

model SomeModel {
    ...
    createdAt         Float           @default(dbgenerated("gen_created_at()"))
    createdAtHK       String          @default(dbgenerated("gen_created_at_hk_timestr()"))
}

Then we execute

npx prisma migrate dev --create-only

to create a migration .sql file for further editing (but not execute the changes).

SQL Functions

Next we fill in the missing implementation of gen_created_at and gen_created_at_hk_timestr in the generated migration .sql file.

-- in generated migration SQL file
CREATE OR REPLACE FUNCTION gen_created_at() RETURNS float as $$
BEGIN
	return ROUND(extract(epoch from NOW()::TIMESTAMPTZ) * 1000, 0)::float;
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gen_created_at_hk_timestr() RETURNS text as $$
BEGIN
	return TO_CHAR((NOW()::TIMESTAMPTZ AT TIME ZONE 'UTC' AT TIME ZONE 'GMT+8'), 'YYYY-MM-DD HH24:MI:SS');
END
$$
LANGUAGE plpgsql;

-- prisma should have generated something similar:
ALTER TABLE your_table ALTER COLUMN "created_at" SET DEFAULT gen_created_at();
ALTER TABLE your_table ADD COLUMN "createdAtHK" TEXT NOT NULL DEFAULT gen_created_at_hk_timestr();

Now we can start the migration.