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

#!/usr/bin/env tsx
/**
 * 验证 works 和 work_day_statistics 表的时间字段格式
 */
import { initDatabase } from '../models/index.js';
import { AppDataSource } from '../models/index.js';
import { logger } from '../utils/logger.js';

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

    logger.info('\n========================================');
    logger.info('验证时间字段格式...');
    logger.info('========================================\n');

    // 检查 works 表
    logger.info('works 表示例数据:');
    const works = await AppDataSource.query(`
      SELECT id, 
             created_at, 
             updated_at,
             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
      FROM works 
      ORDER BY id DESC 
      LIMIT 5
    `);
    works.forEach((row: any) => {
      logger.info(`  ID ${row.id}:`);
      logger.info(`    created_at (原始): ${row.created_at}`);
      logger.info(`    created_at (格式化): ${row.created_at_formatted}`);
      logger.info(`    updated_at (原始): ${row.updated_at}`);
      logger.info(`    updated_at (格式化): ${row.updated_at_formatted}`);
    });

    // 检查 work_day_statistics 表
    logger.info('\nwork_day_statistics 表示例数据:');
    const stats = await AppDataSource.query(`
      SELECT id, 
             created_at, 
             updated_at,
             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
      FROM work_day_statistics 
      ORDER BY id DESC 
      LIMIT 5
    `);
    stats.forEach((row: any) => {
      logger.info(`  ID ${row.id}:`);
      logger.info(`    created_at (原始): ${row.created_at}`);
      logger.info(`    created_at (格式化): ${row.created_at_formatted}`);
      logger.info(`    updated_at (原始): ${row.updated_at}`);
      logger.info(`    updated_at (格式化): ${row.updated_at_formatted}`);
    });

    // 检查表结构
    logger.info('\n检查表结构:');
    const worksStructure = await AppDataSource.query(`
      SHOW COLUMNS FROM works WHERE Field IN ('created_at', 'updated_at')
    `);
    logger.info('works 表字段类型:');
    worksStructure.forEach((col: any) => {
      logger.info(`  ${col.Field}: ${col.Type}`);
    });

    const statsStructure = await AppDataSource.query(`
      SHOW COLUMNS FROM work_day_statistics WHERE Field IN ('created_at', 'updated_at')
    `);
    logger.info('work_day_statistics 表字段类型:');
    statsStructure.forEach((col: any) => {
      logger.info(`  ${col.Field}: ${col.Type}`);
    });

    logger.info('\n========================================');
    logger.info('验证完成！');
    logger.info('========================================\n');

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

verifyTimestampFormat().catch(console.error);