012-kaopeilian/知识库/migrate_timezone_data.sql

-- 考培练系统时区数据迁移脚本
-- 将所有UTC时间戳转换为北京时间（+8小时）
-- 执行前已备份数据库
-- 执行时间：2025-10-17

USE kaopeilian;

-- 设置安全模式为0，允许批量更新
SET SQL_SAFE_UPDATES = 0;

-- 1. 更新 users 表
UPDATE users 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    last_login_at = CASE WHEN last_login_at IS NOT NULL THEN DATE_ADD(last_login_at, INTERVAL 8 HOUR) ELSE NULL END,
    password_changed_at = CASE WHEN password_changed_at IS NOT NULL THEN DATE_ADD(password_changed_at, INTERVAL 8 HOUR) ELSE NULL END,
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 2. 更新 teams 表
UPDATE teams 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 3. 更新 user_teams 表
UPDATE user_teams 
SET joined_at = DATE_ADD(joined_at, INTERVAL 8 HOUR)
WHERE user_id > 0;

-- 4. 更新 courses 表
UPDATE courses 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    published_at = CASE WHEN published_at IS NOT NULL THEN DATE_ADD(published_at, INTERVAL 8 HOUR) ELSE NULL END,
    broadcast_generated_at = CASE WHEN broadcast_generated_at IS NOT NULL THEN DATE_ADD(broadcast_generated_at, INTERVAL 8 HOUR) ELSE NULL END,
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 5. 更新 course_materials 表
UPDATE course_materials 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 6. 更新 course_material_relations 表（如果存在）
-- UPDATE course_material_relations 
-- SET 
--     created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
--     updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
--     deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
-- WHERE id > 0;

-- 7. 更新 knowledge_points 表
UPDATE knowledge_points 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR)
WHERE id > 0;

-- 8. 更新 exams 表
UPDATE exams 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    start_time = DATE_ADD(start_time, INTERVAL 8 HOUR),
    end_time = CASE WHEN end_time IS NOT NULL THEN DATE_ADD(end_time, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 9. 更新 exam_results 表
UPDATE exam_results 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR)
WHERE id > 0;

-- 10. 更新 exam_mistakes 表
UPDATE exam_mistakes 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    mastered_at = CASE WHEN mastered_at IS NOT NULL THEN DATE_ADD(mastered_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 11. 更新 questions 表
UPDATE questions 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR)
WHERE id > 0;

-- 12. 更新 training_scenes 表
UPDATE training_scenes 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 13. 更新 training_sessions 表
UPDATE training_sessions 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    start_time = DATE_ADD(start_time, INTERVAL 8 HOUR),
    end_time = CASE WHEN end_time IS NOT NULL THEN DATE_ADD(end_time, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 14. 更新 training_messages 表
UPDATE training_messages 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR)
WHERE id > 0;

-- 15. 更新 training_reports 表
UPDATE training_reports 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR)
WHERE id > 0;

-- 16. 更新 positions 表（如果存在）
UPDATE positions 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 17. 更新 position_members 表（如果存在）
UPDATE position_members 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    joined_at = DATE_ADD(joined_at, INTERVAL 8 HOUR),
    deleted_at = CASE WHEN deleted_at IS NOT NULL THEN DATE_ADD(deleted_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 18. 更新 system_logs 表（如果存在）
UPDATE system_logs 
SET created_at = DATE_ADD(created_at, INTERVAL 8 HOUR)
WHERE id > 0;

-- 19. 更新 async_tasks 表（如果存在）
UPDATE async_tasks 
SET 
    created_at = DATE_ADD(created_at, INTERVAL 8 HOUR),
    updated_at = DATE_ADD(updated_at, INTERVAL 8 HOUR),
    started_at = CASE WHEN started_at IS NOT NULL THEN DATE_ADD(started_at, INTERVAL 8 HOUR) ELSE NULL END,
    completed_at = CASE WHEN completed_at IS NOT NULL THEN DATE_ADD(completed_at, INTERVAL 8 HOUR) ELSE NULL END
WHERE id > 0;

-- 恢复安全模式
SET SQL_SAFE_UPDATES = 1;

-- 验证：查看更新后的时间
SELECT '时区迁移完成！以下是部分数据验证：' as message;
SELECT id, username, created_at, updated_at FROM users LIMIT 3;
SELECT id, title, created_at, updated_at FROM courses LIMIT 3;
SELECT NOW() as current_beijing_time, UTC_TIMESTAMP() as utc_time;