Migration script for changing datetime back to timestampz from int8

Migration script for changing datetime back to timestampz from int8

From Sails 1.0, sails updated the data type of createdAt and updateAt field from timestampz to int8.

The new config in model.js looks like as follows:

attributes: {
    createdAt: { type: 'number', autoCreatedAt: true, },
    updatedAt: { type: 'number', autoUpdatedAt: true, },
    id: { type: 'number', autoIncrement: true, },
  },

The problem with this is that unix timestamp is far less human-readable than timestampz.

Compare – 1581761177000 with 2020-02-15T10:06:17.000Z . Which one do you think is more readable?

The second problem is that if you use metabase as a business analytics tool, metabase does not recognize int8 as a date easily. Maybe at a really high scale, the integer field for a date might be the right choice. For now, timestampz is far more convenient.

We use postgres. If you want to revert back to timestampz, update the model.js to:

  attributes: {
    createdAt: { type: 'ref', columnType: 'timestamptz', autoCreatedAt: true, },
    updatedAt: { type: 'ref', columnType: 'timestamptz', autoUpdatedAt: true, },
    id: { type: 'number', autoIncrement: true, },
  },

If you are starting with a brand new sails project, you can stop here. However, if you started with a sail 1.0 project without making the above changes, then you will have data in the old format. In this case, in addition to changing the model.js config, you will need to migrate the existing data to timestampz format.

For each table that you have, execute the following script. This script assumes that you have a table called filing . Replace filing with the name of your table for all the tables that you want to migrate.

-- for filing

-- ALTER
ALTER TABLE "public"."filing" ALTER COLUMN "createdAt" SET DATA TYPE text;

-- MIGRATE
WITH g AS (
SELECT
to_timestamp(cast("createdAt" AS bigint) / 1000)::TIMESTAMPTZ AS c, id
FROM
"public"."filing"
)
UPDATE
"public"."filing"
SET
"createdAt" = g.c
FROM
g g where g.id = "public"."filing".id;


ALTER TABLE "public"."filing" ALTER COLUMN "createdAt" SET DATA TYPE TIMESTAMPTZ USING "createdAt"::timestamp WITH time zone;


-- ALTER
ALTER TABLE "public"."filing" ALTER COLUMN "updatedAt" SET DATA TYPE text;

-- MIGRATE
WITH g AS (
SELECT
to_timestamp(cast("updatedAt" AS bigint) / 1000)::TIMESTAMPTZ AS c, id
FROM
"public"."filing"
)
UPDATE
"public"."filing"
SET
"updatedAt" = g.c
FROM
g g where g.id = "public"."filing".id;


ALTER TABLE "public"."filing" ALTER COLUMN "updatedAt" SET DATA TYPE TIMESTAMPTZ USING "updatedAt"::timestamp WITH time zone;
Alex J V
Posted on:
Post author

Leave a comment

Your email address will not be published. Required fields are marked *