multi-platform-media-manage/database/migrations/fix_platform_accounts_timestamp_format.sql

-- 修复 platform_accounts 表的 created_at 和 updated_at 时间格式
-- 将 TIMESTAMP 类型改为 DATETIME 类型，确保时间格式为 2026-02-05 12:22:22
-- 执行日期: 2026-02-05

USE media_manager;

-- 步骤1: 设置会话时区为东八区（确保时间转换正确）
SET time_zone = '+08:00';

-- 步骤2: 修改 created_at 字段类型为 DATETIME
-- MySQL 会自动将 TIMESTAMP 转换为 DATETIME，保持时间值不变
ALTER TABLE platform_accounts 
MODIFY COLUMN created_at DATETIME NULL;

ALTER TABLE platform_accounts 
MODIFY COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;

-- 步骤3: 修改 updated_at 字段类型为 DATETIME
ALTER TABLE platform_accounts 
MODIFY COLUMN updated_at DATETIME NULL;

ALTER TABLE platform_accounts 
MODIFY COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- 步骤4: 验证修改结果
DESCRIBE platform_accounts;

SELECT 
    id, 
    account_name, 
    platform,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_at_formatted,
    DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i:%s') AS updated_at_formatted,
    created_at,
    updated_at
FROM platform_accounts 
ORDER BY id DESC 
LIMIT 10;