README

Database-agnostic SQL expression helper for date truncation, extraction, and differences across SQLite and MySQL.

Requirements

• PHP 8.2+
• Laravel 11 or 12

Installation

composer require philiprehberger/laravel-db-expressions

The service provider and facade are registered automatically via Laravel's package discovery.

Usage

All methods are static and return plain SQL strings suitable for use in Eloquent's selectRaw, groupByRaw, orderByRaw, and whereRaw calls.

Date Truncation (for GROUP BY buckets)

Group records into time buckets using the dateTrunc* methods or the general-purpose dateFormat dispatcher.

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');

Real Eloquent query example:

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();

Date Part Extraction (integer values)

Extract individual date components as integers.

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');

Real Eloquent query example:

// 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();

Date Differences

Calculate the difference between two datetime columns.

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');

Real Eloquent query example:

// 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');

Date Arithmetic

Add or subtract days from a datetime column.

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);

Real Eloquent query example:

// 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();

Facade

You can also use the DbExpressions facade:

use PhilipRehberger\DbExpressions\Facades\DbExpressions;

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

Driver Detection

use PhilipRehberger\DbExpressions\DatabaseExpressions;

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

Multi-Driver Support

Security

All $column parameters are validated against the pattern [a-zA-Z0-9_.]+ before being interpolated into SQL. Passing an invalid column name (e.g. user-supplied input) throws an InvalidArgumentException. Never pass raw user input as a column name.

Known Limitations

Week Number Semantics

The dateTruncWeek() and extractWeek() methods produce slightly different week numbers between SQLite and MySQL:

If exact cross-driver parity is required for week numbers, consider using dateTruncDay() and computing week buckets in application code.

dateFormat() Throws on Invalid Periods

The dateFormat() dispatcher throws an InvalidArgumentException if the period is not one of: hour, day, week, month, year. Validate user input before passing it to this method.

API

Development

composer install
vendor/bin/phpunit
vendor/bin/pint --test
vendor/bin/phpstan analyse

Support

If you find this project useful:

⭐ Star the repo

🐛 Report issues

💡 Suggest features

❤️ Sponsor development

🌐 All Open Source Projects

💻 GitHub Profile

🔗 LinkedIn Profile

License

MIT