73 lines
2.6 KiB
PL/PgSQL
73 lines
2.6 KiB
PL/PgSQL
-- Add ISO 8601 compliant timestamp columns to blocks and transactions
|
|
-- This migration adds timestamp_iso columns that store ISO 8601 formatted timestamps
|
|
|
|
-- Add timestamp_iso column to blocks table
|
|
ALTER TABLE blocks ADD COLUMN IF NOT EXISTS timestamp_iso VARCHAR(30);
|
|
|
|
-- Create index for timestamp_iso on blocks
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_chain_timestamp_iso ON blocks(chain_id, timestamp_iso);
|
|
|
|
-- Add timestamp_iso column to transactions table
|
|
-- This will be populated from the block timestamp via trigger
|
|
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS timestamp_iso VARCHAR(30);
|
|
|
|
-- Create index for timestamp_iso on transactions
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_chain_timestamp_iso ON transactions(chain_id, timestamp_iso);
|
|
|
|
-- Function to update timestamp_iso from timestamp
|
|
CREATE OR REPLACE FUNCTION update_timestamp_iso()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.timestamp_iso := to_char(NEW.timestamp, 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger to automatically update timestamp_iso when timestamp changes in blocks
|
|
DROP TRIGGER IF EXISTS trigger_blocks_timestamp_iso ON blocks;
|
|
CREATE TRIGGER trigger_blocks_timestamp_iso
|
|
BEFORE INSERT OR UPDATE OF timestamp ON blocks
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_timestamp_iso();
|
|
|
|
-- Function to update transaction timestamp_iso from block timestamp
|
|
CREATE OR REPLACE FUNCTION update_transaction_timestamp_iso()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
block_timestamp TIMESTAMP;
|
|
BEGIN
|
|
-- Get the block timestamp
|
|
SELECT b.timestamp INTO block_timestamp
|
|
FROM blocks b
|
|
WHERE b.chain_id = NEW.chain_id AND b.number = NEW.block_number;
|
|
|
|
-- If block timestamp exists, format it as ISO 8601
|
|
IF block_timestamp IS NOT NULL THEN
|
|
NEW.timestamp_iso := to_char(block_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger to automatically update timestamp_iso when transaction is inserted/updated
|
|
DROP TRIGGER IF EXISTS trigger_transactions_timestamp_iso ON transactions;
|
|
CREATE TRIGGER trigger_transactions_timestamp_iso
|
|
BEFORE INSERT OR UPDATE OF block_number ON transactions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_transaction_timestamp_iso();
|
|
|
|
-- Backfill existing blocks with ISO timestamps
|
|
UPDATE blocks
|
|
SET timestamp_iso = to_char(timestamp, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
|
|
WHERE timestamp_iso IS NULL;
|
|
|
|
-- Backfill existing transactions with ISO timestamps from blocks
|
|
UPDATE transactions t
|
|
SET timestamp_iso = to_char(b.timestamp, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
|
|
FROM blocks b
|
|
WHERE t.chain_id = b.chain_id
|
|
AND t.block_number = b.number
|
|
AND t.timestamp_iso IS NULL;
|
|
|