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

Method	SQLite	MySQL / MariaDB
`dateTruncHour`	`strftime('%Y-%m-%d %H:00:00', col)`	`DATE_FORMAT(col, '%Y-%m-%d %H:00:00')`
`dateTruncDay`	`strftime('%Y-%m-%d', col)`	`DATE_FORMAT(col, '%Y-%m-%d')`
`dateTruncWeek`	`strftime('%Y-%W', col)`	`DATE_FORMAT(col, '%Y-%u')`
`dateTruncMonth`	`strftime('%Y-%m', col)`	`DATE_FORMAT(col, '%Y-%m')`
`dateTruncYear`	`strftime('%Y', col)`	`DATE_FORMAT(col, '%Y')`
`extractHour`	`CAST(strftime('%H', col) AS INTEGER)`	`HOUR(col)`
`extractDay`	`CAST(strftime('%d', col) AS INTEGER)`	`DAY(col)`
`extractWeek`	`CAST(strftime('%W', col) AS INTEGER)`	`WEEK(col)`
`extractMonth`	`CAST(strftime('%m', col) AS INTEGER)`	`MONTH(col)`
`extractYear`	`CAST(strftime('%Y', col) AS INTEGER)`	`YEAR(col)`
`extractQuarter`	`((CAST(strftime('%m', col) AS INTEGER) - 1) / 3) + 1`	`QUARTER(col)`
`extractMinute`	`CAST(strftime('%M', col) AS INTEGER)`	`MINUTE(col)`
`extractSecond`	`CAST(strftime('%S', col) AS INTEGER)`	`SECOND(col)`
`addDays`	`datetime(col, '+N days')`	`DATE_ADD(col, INTERVAL N DAY)`
`subtractDays`	`datetime(col, '-N days')`	`DATE_SUB(col, INTERVAL N DAY)`
`dateDiffDays`	`CAST((julianday(c1) - julianday(c2)) AS INTEGER)`	`DATEDIFF(c1, c2)`
`dateDiffHours`	`(julianday(c1) - julianday(c2)) * 24`	`TIMESTAMPDIFF(HOUR, c2, c1)`

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:

Driver	`dateTruncWeek` format	`extractWeek` function	Week start
SQLite	`strftime('%W')` — Monday-based, 00–53	`strftime('%W')` — Monday-based, 00–53	Monday
MySQL	`DATE_FORMAT('%u')` — Monday-based, 01–53	`WEEK()` — mode 0, Sunday-based, 0–53	Varies

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

Method	Description
`DatabaseExpressions::dateTruncHour(string $column): string`	SQL expression for hourly time bucket
`DatabaseExpressions::dateTruncDay(string $column): string`	SQL expression for daily time bucket
`DatabaseExpressions::dateTruncWeek(string $column): string`	SQL expression for weekly time bucket
`DatabaseExpressions::dateTruncMonth(string $column): string`	SQL expression for monthly time bucket
`DatabaseExpressions::dateTruncYear(string $column): string`	SQL expression for yearly time bucket
`DatabaseExpressions::dateFormat(string $column, string $period): string`	General dispatcher for date truncation; throws on invalid period
`DatabaseExpressions::extractHour(string $column): string`	Extract hour of day as integer (0–23)
`DatabaseExpressions::extractDay(string $column): string`	Extract day of month as integer (1–31)
`DatabaseExpressions::extractWeek(string $column): string`	Extract week number as integer (0–53)
`DatabaseExpressions::extractMonth(string $column): string`	Extract month as integer (1–12)
`DatabaseExpressions::extractYear(string $column): string`	Extract year as integer
`DatabaseExpressions::extractQuarter(string $column): string`	Extract quarter as integer (1–4)
`DatabaseExpressions::extractMinute(string $column): string`	Extract minute as integer (0–59)
`DatabaseExpressions::extractSecond(string $column): string`	Extract second as integer (0–59)
`DatabaseExpressions::addDays(string $column, int $days): string`	Add days to a datetime column
`DatabaseExpressions::subtractDays(string $column, int $days): string`	Subtract days from a datetime column
`DatabaseExpressions::dateDiffDays(string $col1, string $col2): string`	Difference between two date columns in whole days
`DatabaseExpressions::dateDiffHours(string $col1, string $col2): string`	Difference between two date columns in hours
`DatabaseExpressions::driver(): string`	Return the current DB driver name
`DatabaseExpressions::isSqlite(): bool`	Whether the current connection is SQLite

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

philiprehberger / laravel-db-expressions

Maintainers

Package info

Fund package maintenance!

Statistics

Security

README

Requirements

Installation

Usage

Date Truncation (for GROUP BY buckets)

Date Part Extraction (integer values)

Date Differences

Date Arithmetic

Facade

Driver Detection

Multi-Driver Support

Security

Known Limitations

Week Number Semantics

dateFormat() Throws on Invalid Periods

API

Development

Support

License