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

import { AppDataSource } from '../models/index.js';
import { logger } from '../utils/logger.js';

async function checkTimestampFormat() {
  try {
    await AppDataSource.initialize();
    logger.info('[Check] Database connected');

    // 查看表结构
    const tableStructure = await AppDataSource.query(`
      SELECT 
        COLUMN_NAME,
        DATA_TYPE,
        COLUMN_TYPE,
        COLUMN_DEFAULT,
        IS_NULLABLE
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'user_day_statistics'
        AND COLUMN_NAME IN ('created_at', 'updated_at')
      ORDER BY COLUMN_NAME
    `);

    logger.info('[Check] Table structure:');
    for (const col of tableStructure) {
      logger.info(`[Check]   ${col.COLUMN_NAME}:`);
      logger.info(`[Check]     DATA_TYPE: ${col.DATA_TYPE}`);
      logger.info(`[Check]     COLUMN_TYPE: ${col.COLUMN_TYPE}`);
      logger.info(`[Check]     DEFAULT: ${col.COLUMN_DEFAULT}`);
    }

    // 查看实际数据
    logger.info('[Check] Sample data (raw):');
    const sampleData = await AppDataSource.query(`
      SELECT 
        id,
        account_id,
        record_date,
        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 user_day_statistics
      ORDER BY id DESC
      LIMIT 10
    `);

    for (const row of sampleData) {
      logger.info(`[Check]   ID ${row.id}:`);
      logger.info(`[Check]     created_at (raw): ${row.created_at}`);
      logger.info(`[Check]     created_at (formatted): ${row.created_at_formatted}`);
      logger.info(`[Check]     updated_at (raw): ${row.updated_at}`);
      logger.info(`[Check]     updated_at (formatted): ${row.updated_at_formatted}`);
    }

    // 检查是否有 TIMESTAMP 类型的数据需要转换
    const timestampCheck = await AppDataSource.query(`
      SELECT COUNT(*) as count
      FROM user_day_statistics
      WHERE created_at IS NOT NULL
    `);

    logger.info(`[Check] Total records with timestamps: ${timestampCheck[0].count}`);

    process.exit(0);
  } catch (error) {
    logger.error('[Check] Error:', error);
    process.exit(1);
  } finally {
    if (AppDataSource.isInitialized) {
      await AppDataSource.destroy();
    }
  }
}

void checkTimestampFormat();