PHP code example of philiprehberger / laravel-db-expressions

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

philiprehberger / laravel-db-expressions example snippets

use PhilipRehberger\DbExpressions\DatabaseExpressions;

// Hourly buckets: '2026-03-05 14:00:00'
$expr = DatabaseExpressions::dateTruncHour('created_at');

// Daily buckets: '2026-03-05'
$expr = DatabaseExpressions::dateTruncDay('created_at');

// Weekly buckets: '2026-09'
$expr = DatabaseExpressions::dateTruncWeek('created_at');

// Monthly buckets: '2026-03'
$expr = DatabaseExpressions::dateTruncMonth('created_at');

// Yearly buckets: '2026'
$expr = DatabaseExpressions::dateTruncYear('created_at');

// General dispatcher — throws `InvalidArgumentException` for unknown periods
$expr = DatabaseExpressions::dateFormat('created_at', 'week');

use PhilipRehberger\DbExpressions\DatabaseExpressions;

$period = 'month'; // from request, e.g. hour|day|week|month|year

$results = Invoice::query()
    ->selectRaw(DatabaseExpressions::dateFormat('created_at', $period) . ' as period, SUM(total) as revenue')
    ->groupByRaw(DatabaseExpressions::dateFormat('created_at', $period))
    ->orderByRaw(DatabaseExpressions::dateFormat('created_at', $period))
    ->get();

use PhilipRehberger\DbExpressions\DatabaseExpressions;

// Hour of day: 0–23
$expr = DatabaseExpressions::extractHour('created_at');

// Day of month: 1–31
$expr = DatabaseExpressions::extractDay('created_at');

// Week number: 0–53
$expr = DatabaseExpressions::extractWeek('created_at');

// Month: 1–12
$expr = DatabaseExpressions::extractMonth('created_at');

// Year: e.g. 2026
$expr = DatabaseExpressions::extractYear('created_at');

// Quarter: 1–4
$expr = DatabaseExpressions::extractQuarter('created_at');

// Minute: 0–59
$expr = DatabaseExpressions::extractMinute('created_at');

// Second: 0–59
$expr = DatabaseExpressions::extractSecond('created_at');

// Find which hour of the day has the most activity
$results = ApiUsageLog::query()
    ->selectRaw(DatabaseExpressions::extractHour('created_at') . ' as hour, COUNT(*) as hits')
    ->groupByRaw(DatabaseExpressions::extractHour('created_at'))
    ->orderByRaw(DatabaseExpressions::extractHour('created_at'))
    ->get();

use PhilipRehberger\DbExpressions\DatabaseExpressions;

// Difference in whole days (column1 - column2)
$expr = DatabaseExpressions::dateDiffDays('completed_at', 'created_at');

// Difference in hours (column1 - column2)
$expr = DatabaseExpressions::dateDiffHours('completed_at', 'created_at');

// Average project duration in days
$avg = Project::query()
    ->whereNotNull('completed_at')
    ->selectRaw('AVG(' . DatabaseExpressions::dateDiffDays('completed_at', 'created_at') . ') as avg_days')
    ->value('avg_days');

use PhilipRehberger\DbExpressions\DatabaseExpressions;

// Add 7 days to a date
$expr = DatabaseExpressions::addDays('created_at', 7);

// Subtract 30 days from a date
$expr = DatabaseExpressions::subtractDays('created_at', 30);

// Find records expiring within the next 7 days
$expiring = Subscription::query()
    ->whereRaw(DatabaseExpressions::addDays('created_at', 365) . ' < NOW()')
    ->get();

// Get records from the last 30 days using date arithmetic
$recent = Order::query()
    ->whereRaw('created_at >= ' . DatabaseExpressions::subtractDays('NOW()', 30))
    ->get();

use PhilipRehberger\DbExpressions\Facades\DbExpressions;

$expr = DbExpressions::dateTruncMonth('created_at');
$expr = DbExpressions::extractQuarter('invoiced_at');
$expr = DbExpressions::dateDiffDays('due_at', 'created_at');

use PhilipRehberger\DbExpressions\DatabaseExpressions;

$driver = DatabaseExpressions::driver();   // 'sqlite', 'mysql', etc.
$isSqlite = DatabaseExpressions::isSqlite(); // bool