Skip to content

Instantly share code, notes, and snippets.

@GrapeApple0

GrapeApple0/rollback-1697420555911-deleteCreatedAt.sql

Forked from u1-liquid/rollback-1697420555911-deleteCreatedAt.sql
Last active July 15, 2024 10:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save GrapeApple0/00ca640d3555331322a59e433ffe91f8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save GrapeApple0/00ca640d3555331322a59e433ffe91f8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
rollback-1697420555911-deleteCreatedAt.sql
CREATE OR REPLACE FUNCTION base36_decode(encoded_text text)
RETURNS bigint AS $$
declare
k_base constant integer := 36;
k_alphabet constant text := '0123456789abcdefghijklmnopqrstuvwxyz';
v_encoded_arr text[];
v_return_result bigint := 0;
v_interim bigint;
v_index integer;
v_token text;
v_power integer := 0;
begin
if encoded_text is null or length( encoded_text ) = 0 then
return null;
end if;
v_encoded_arr := string_to_array( reverse( encoded_text ) , null );
foreach v_token in array v_encoded_arr
loop
v_index := strpos( k_alphabet, v_token );
if v_index = 0 then
raise notice 'Token ignored "%"', v_token;
else
v_return_result := v_return_result + ( ( v_index - 1) * pow( k_base, v_power) );
v_power := 1 + v_power;
end if;
end loop;
return v_return_result;
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment