search

romanzipp / Laravel-Queue-Monitor

Monitoring Laravel Jobs with your Database
https://packagist.org/packages/romanzipp/laravel-queue-monitor
MIT License
698 stars 92 forks source link

Compatibility with Azure SQL/SQLServer #117

Closed aokrasavin closed 1 year ago

aokrasavin commented 1 year ago

My system (SmartHydration) is deployed on Azure and uses a full Azure SQL as database (which is a variation of SqlServer 19).

$aggregationColumns (line 91 of ShowQueueMonitorController.php) is hardcoded to use MySql-specific syntax. It should be changed to be more generic. I leave it to you for the best solution, but the SQL for SQLServer should be

    $aggregationColumns = [
        DB::raw('COUNT(*) as count'),
        DB::raw('SUM(DATEDIFF(SECOND, started_at, finished_at)) as total_time_elapsed'),
        DB::raw('AVG(DATEDIFF(SECOND, started_at, finished_at)) as average_time_elapsed'),
    ];

As far as I could tell, this is the only part of the component with incompatibility - everything else works out of the box.

romanzipp commented 1 year ago

Thanks for the report! I just quickly looked through the SQLServer docs and it seems like DATEDIFF does not support second differences, can you confirm that?

aokrasavin commented 1 year ago

In SQLServer DATEDIFF (despite its somewhat misleading name LOL) supports time differences up to an including nanoseconds. The code above is the actual fix I implemented locally which works.

See here https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql

romanzipp commented 1 year ago

I've added a conditional check to alter the diff function name on SQL Server in #118 . Does this work four you?