multi-platform-media-manage/server/src/scripts/fix-timestamp-format.ts

#!/usr/bin/env tsx
/**
 * 修复 works 和 work_day_statistics 表的 created_at 和 updated_at 字段格式
 * 将 timestamp 类型改为 datetime 类型，并修复历史数据
 * 
 * 运行: cd server && pnpm exec tsx src/scripts/fix-timestamp-format.ts
 */
import { initDatabase } from '../models/index.js';
import { AppDataSource } from '../models/index.js';
import { logger } from '../utils/logger.js';

async function fixTimestampFormat() {
  try {
    await initDatabase();
    logger.info('数据库连接已初始化');

    const queryRunner = AppDataSource.createQueryRunner();
    await queryRunner.connect();

    try {
      logger.info('\n========================================');
      logger.info('开始修复时间字段格式...');
      logger.info('========================================\n');

      // 1. 修改 works 表的字段类型
      logger.info('1. 修改 works 表的 created_at 和 updated_at 字段类型...');
      await queryRunner.query(`
        ALTER TABLE works 
        MODIFY COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        MODIFY COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      `);
      logger.info('   ✓ works 表字段类型修改完成');

      // 2. 修改 work_day_statistics 表的字段类型
      logger.info('2. 修改 work_day_statistics 表的 created_at 和 updated_at 字段类型...');
      await queryRunner.query(`
        ALTER TABLE work_day_statistics 
        MODIFY COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        MODIFY COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      `);
      logger.info('   ✓ work_day_statistics 表字段类型修改完成');

      // 3. 修复历史数据 - 将时间转换为正确的格式
      logger.info('\n3. 修复 works 表的历史数据...');
      const worksResult = await queryRunner.query(`
        UPDATE works 
        SET created_at = CONVERT_TZ(created_at, @@session.time_zone, '+08:00'),
            updated_at = CONVERT_TZ(updated_at, @@session.time_zone, '+08:00')
        WHERE created_at IS NOT NULL OR updated_at IS NOT NULL
      `);
      logger.info(`   ✓ works 表已更新 ${worksResult.affectedRows || 0} 条记录`);

      logger.info('\n4. 修复 work_day_statistics 表的历史数据...');
      const statsResult = await queryRunner.query(`
        UPDATE work_day_statistics 
        SET created_at = CONVERT_TZ(created_at, @@session.time_zone, '+08:00'),
            updated_at = CONVERT_TZ(updated_at, @@session.time_zone, '+08:00')
        WHERE created_at IS NOT NULL OR updated_at IS NOT NULL
      `);
      logger.info(`   ✓ work_day_statistics 表已更新 ${statsResult.affectedRows || 0} 条记录`);

      // 4. 验证修复结果
      logger.info('\n5. 验证修复结果...');
      const sampleWorks = await queryRunner.query(`
        SELECT id, created_at, updated_at 
        FROM works 
        ORDER BY id DESC 
        LIMIT 5
      `);
      logger.info('   works 表示例数据:');
      sampleWorks.forEach((row: any) => {
        logger.info(`     ID ${row.id}: created_at=${row.created_at}, updated_at=${row.updated_at}`);
      });

      const sampleStats = await queryRunner.query(`
        SELECT id, created_at, updated_at 
        FROM work_day_statistics 
        ORDER BY id DESC 
        LIMIT 5
      `);
      logger.info('   work_day_statistics 表示例数据:');
      sampleStats.forEach((row: any) => {
        logger.info(`     ID ${row.id}: created_at=${row.created_at}, updated_at=${row.updated_at}`);
      });

      logger.info('\n========================================');
      logger.info('时间字段格式修复完成！');
      logger.info('========================================\n');

    } catch (error: any) {
      logger.error('修复过程中出错:', error);
      throw error;
    } finally {
      await queryRunner.release();
    }

  } catch (error: any) {
    logger.error('修复失败:', error);
    if (error instanceof Error) {
      logger.error('错误堆栈:', error.stack);
    }
    process.exit(1);
  } finally {
    await AppDataSource.destroy();
    process.exit(0);
  }
}

fixTimestampFormat().catch(console.error);