Rollback https://github.com/misskey-dev/misskey/blob/6bd78770de06bd3694127da17ccd051f05057329/packages/backend/migration/1697420555911-deleteCreatedAt.js

rollback-1697420555911-deleteCreatedAt.sql

CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
    RETURNS bigint AS $$
DECLARE
    a char[];
    ret bigint;
    i int;
    val int;
    chars varchar;
BEGIN
    chars := '0123456789abcdefghijklmnopqrstuvwxyz';

    FOR i IN REVERSE char_length(base36)..1 LOOP
            a := a || substring(upper(base36) FROM i FOR 1)::char;
        END LOOP;
    i := 0;
    ret := 0;
    WHILE i < (array_length(a,1)) LOOP
            val := position(a[i+1] IN chars)-1;
            ret := ret + (val * (36 ^ i));
            i := i + 1;
        END LOOP;

    RETURN ret;

END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION parse_aid(id text) RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    -- IDの先頭8文字を36進数として解釈し、基準時間を加算してUNIXエポック秒に変換
    base_time bigint := base36_decode(SUBSTRING(id, 1, 8)) + 946684800000;
BEGIN
    -- UNIXエポックからの秒単位で時間を計算し、TIMESTAMP WITH TIME ZONE型で返す
    RETURN TO_TIMESTAMP(base_time / 1000);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE "flash_like" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "flash_like" SET "createdAt" = parse_aid("id");
ALTER TABLE "flash" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "flash" SET "createdAt" = parse_aid("id");
ALTER TABLE "role_assignment" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "role_assignment" SET "createdAt" = parse_aid("id");
ALTER TABLE "role" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "role" SET "createdAt" = parse_aid("id");
ALTER TABLE "webhook" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "webhook" SET "createdAt" = parse_aid("id");
ALTER TABLE "user_pending" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "user_pending" SET "createdAt" = parse_aid("id");
ALTER TABLE "user_note_pining" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "user_note_pining" SET "createdAt" = parse_aid("id");
ALTER TABLE "user_list_membership" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "user_list_membership" SET "createdAt" = parse_aid("id");
ALTER TABLE "user_list_favorite" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "user_list_favorite" SET "createdAt" = parse_aid("id");
ALTER TABLE "sw_subscription" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "sw_subscription" SET "createdAt" = parse_aid("id");
ALTER TABLE "signin" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "signin" SET "createdAt" = parse_aid("id");
ALTER TABLE "registry_item" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "registry_item" SET "createdAt" = parse_aid("id");
ALTER TABLE "registration_ticket" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "registration_ticket" SET "createdAt" = parse_aid("id");
ALTER TABLE "promo_read" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "promo_read" SET "createdAt" = parse_aid("id");
ALTER TABLE "poll_vote" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "poll_vote" SET "createdAt" = parse_aid("id");
ALTER TABLE "password_reset_request" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "password_reset_request" SET "createdAt" = parse_aid("id");
ALTER TABLE "page_like" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "page_like" SET "createdAt" = parse_aid("id");
ALTER TABLE "page" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "page" SET "createdAt" = parse_aid("id");
ALTER TABLE "note_thread_muting" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "note_thread_muting" SET "createdAt" = parse_aid("id");
ALTER TABLE "note_reaction" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "note_reaction" SET "createdAt" = parse_aid("id");
ALTER TABLE "note_favorite" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "note_favorite" SET "createdAt" = parse_aid("id");
ALTER TABLE "renote_muting" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "renote_muting" SET "createdAt" = parse_aid("id");
ALTER TABLE "muting" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "muting" SET "createdAt" = parse_aid("id");
ALTER TABLE "moderation_log" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "moderation_log" SET "createdAt" = parse_aid("id");
ALTER TABLE "gallery_like" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "gallery_like" SET "createdAt" = parse_aid("id");
ALTER TABLE "gallery_post" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "gallery_post" SET "createdAt" = parse_aid("id");
ALTER TABLE "follow_request" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "follow_request" SET "createdAt" = parse_aid("id");
ALTER TABLE "following" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "following" SET "createdAt" = parse_aid("id");
ALTER TABLE "clip_favorite" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "clip_favorite" SET "createdAt" = parse_aid("id");
ALTER TABLE "note" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "note" SET "createdAt" = parse_aid("id");
ALTER TABLE "clip" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "clip" SET "createdAt" = parse_aid("id");
ALTER TABLE "channel_favorite" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "channel_favorite" SET "createdAt" = parse_aid("id");
ALTER TABLE "channel_following" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "channel_following" SET "createdAt" = parse_aid("id");
ALTER TABLE "channel" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "channel" SET "createdAt" = parse_aid("id");
ALTER TABLE "blocking" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "blocking" SET "createdAt" = parse_aid("id");
ALTER TABLE "auth_session" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "auth_session" SET "createdAt" = parse_aid("id");
ALTER TABLE "antenna" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "antenna" SET "createdAt" = parse_aid("id");
ALTER TABLE "user_list" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "user_list" SET "createdAt" = parse_aid("id");
ALTER TABLE "announcement_read" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "announcement_read" SET "createdAt" = parse_aid("id");
ALTER TABLE "announcement" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "announcement" SET "createdAt" = parse_aid("id");
ALTER TABLE "ad" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "ad" SET "createdAt" = parse_aid("id");
ALTER TABLE "access_token" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "access_token" SET "createdAt" = parse_aid("id");
ALTER TABLE "app" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "app" SET "createdAt" = parse_aid("id");
ALTER TABLE "abuse_user_report" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "abuse_user_report" SET "createdAt" = parse_aid("id");
ALTER TABLE "user" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "user" SET "createdAt" = parse_aid("id");
ALTER TABLE "drive_file" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "drive_file" SET "createdAt" = parse_aid("id");
ALTER TABLE "drive_folder" ADD "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW();
UPDATE "drive_folder" SET "createdAt" = parse_aid("id");

DROP FUNCTION parse_aid;
DROP FUNCTION base36_decode;

CREATE INDEX CONCURRENTLY "IDX_149d2e44785707548c82999b01" ON "flash" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_0fb627e1c2f753262a74f0562d" ON "poll_vote" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_fbb4297c927a9b85e9cefa2eb1" ON "page" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_d1259a2c2b7bb413ff449e8711" ON "renote_muting" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_f86d57fbca33c7a4e6897490cc" ON "muting" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_8f1a239bd077c8864a20c62c2c" ON "gallery_post" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_582f8fab771a9040a12961f3e7" ON "following" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_735a5544f9249d412255f47f95" ON "channel_favorite" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_11e71f2511589dcc8a4d3214f9" ON "channel_following" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_71cb7b435b7c0d4843317e7e16" ON "channel" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_b9a354f7941c1e779f3b33aea6" ON "blocking" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_118ec703e596086fc4515acb39" ON "announcement" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_1129c2ef687fc272df040bafaa" ON "ad" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_048a757923ed8b157e9895da53" ON "app" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_db2098070b2b5a523c58181f74" ON "abuse_user_report" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_e11e649824a45d8ed01d597fd9" ON "user" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_c8dfad3b72196dd1d6b5db168a" ON "drive_file" ("createdAt");
CREATE INDEX CONCURRENTLY "IDX_02878d441ceae15ce060b73daf" ON "drive_folder" ("createdAt");

DELETE FROM migrations WHERE "name" = 'DeleteCreatedAt1697420555911';

When I tried this code, the createdAt value wrongly converted. So I wrote a new function for me.

If anyone facing same issue with me, try this function.

CREATE OR REPLACE FUNCTION base36_decode(value TEXT)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 0;
    digit INT;
    len INT;
    i INT;
    char CHAR;
    base INT := 36;
BEGIN
    -- Length of the input string
    len := LENGTH(value);

    -- Iterate through each character in the string
    FOR i IN 1..len LOOP
        char := SUBSTRING(value FROM i FOR 1);

        -- Determine the numeric value of the character
        IF char ~ '[0-9]' THEN
            digit := CAST(char AS INT);
        ELSIF char ~ '[A-Z]' THEN
            digit := ASCII(char) - ASCII('A') + 10;
        ELSIF char ~ '[a-z]' THEN
            digit := ASCII(char) - ASCII('a') + 10;
        ELSE
            RAISE EXCEPTION 'Invalid character in base-36 value: %', char;
        END IF;

        -- Update the result by multiplying by the base and adding the digit
        result := result * base + digit;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;