SetTimestampPrecisionCommand

SetTimestampPrecisionCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Symfony\Component\Console\Attribute\AsCommand;

#[AsCommand(name: 'db:timestamp-precision')]
class SetTimestampPrecisionCommand extends Command
{
    protected $signature = 'db:timestamp-precision {precision=3 : Precision (0-6)}';

    protected $description = 'Set timestamp precision for all timestamp columns in all tables';

    public function handle(): int
    {
        $precision = (int) $this->argument('precision');

        if ($precision < 0 || $precision > 6) {
            $this->error('Precision must be between 0 and 6.');

            return self::FAILURE;
        }

        $columns = DB::select("
            SELECT table_name, column_name
            FROM information_schema.columns
            WHERE table_schema = 'public'
              AND data_type = 'timestamp without time zone'
            ORDER BY table_name, column_name
        ");

        if (empty($columns)) {
            $this->info('No timestamp columns found.');

            return self::SUCCESS;
        }

        $this->info("Setting precision to {$precision} for " . count($columns) . ' columns...');

        foreach ($columns as $column) {
            $sql = sprintf(
                'ALTER TABLE %s ALTER COLUMN %s TYPE timestamp(%d)',
                $column->table_name,
                $column->column_name,
                $precision
            );

            DB::statement($sql);
            $this->line("  - {$column->table_name}.{$column->column_name}");
        }

        $this->newLine();
        $this->info('Done!');

        return self::SUCCESS;
    }
}