PHPackages                             philiprehberger/laravel-db-expressions - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. philiprehberger/laravel-db-expressions

ActiveLibrary[Database & ORM](/categories/database)

philiprehberger/laravel-db-expressions
======================================

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

v1.1.4(1mo ago)116[1 PRs](https://github.com/philiprehberger/laravel-db-expressions/pulls)MITPHPPHP ^8.2CI passing

Since Mar 6Pushed 1mo agoCompare

[ Source](https://github.com/philiprehberger/laravel-db-expressions)[ Packagist](https://packagist.org/packages/philiprehberger/laravel-db-expressions)[ Docs](https://github.com/philiprehberger/laravel-db-expressions)[ RSS](/packages/philiprehberger-laravel-db-expressions/feed)WikiDiscussions main Synced 1mo ago

READMEChangelogDependencies (7)Versions (7)Used By (0)

Laravel DB Expressions
======================

[](#laravel-db-expressions)

[![Tests](https://github.com/philiprehberger/laravel-db-expressions/actions/workflows/tests.yml/badge.svg)](https://github.com/philiprehberger/laravel-db-expressions/actions/workflows/tests.yml)[![Latest Version on Packagist](https://camo.githubusercontent.com/cce874193a6b02d0c4f73f17592b50593b13e65059c96b0461c4f5da3c6bd7ab/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7068696c69707265686265726765722f6c61726176656c2d64622d65787072657373696f6e732e737667)](https://packagist.org/packages/philiprehberger/laravel-db-expressions)[![License](https://camo.githubusercontent.com/4cb4c6cb35894a4ea9713c46acc827591d47ceb452b4c2f7d1520cb46db4c706/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f7068696c69707265686265726765722f6c61726176656c2d64622d65787072657373696f6e73)](LICENSE)

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

Requirements
------------

[](#requirements)

- PHP 8.2+
- Laravel 11 or 12

Installation
------------

[](#installation)

```
composer require philiprehberger/laravel-db-expressions
```

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

Usage
-----

[](#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)

[](#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)

[](#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');
```

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

[](#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');
```

### Facade

[](#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

[](#driver-detection)

```
use PhilipRehberger\DbExpressions\DatabaseExpressions;

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

### Multi-Driver Support

[](#multi-driver-support)

MethodSQLiteMySQL / 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)``dateDiffDays``CAST((julianday(c1) - julianday(c2)) AS INTEGER)``DATEDIFF(c1, c2)``dateDiffHours``(julianday(c1) - julianday(c2)) * 24``TIMESTAMPDIFF(HOUR, c2, c1)`### Security

[](#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

[](#known-limitations)

### Week Number Semantics

[](#week-number-semantics)

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

Driver`dateTruncWeek` format`extractWeek` functionWeek startSQLite`strftime('%W')` — Monday-based, 00–53`strftime('%W')` — Monday-based, 00–53MondayMySQL`DATE_FORMAT('%u')` — Monday-based, 01–53`WEEK()` — mode 0, Sunday-based, 0–53VariesIf exact cross-driver parity is required for week numbers, consider using `dateTruncDay()` and computing week buckets in application code.

### dateFormat() Throws on Invalid Periods

[](#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
---

[](#api)

MethodDescription`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::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 SQLiteDevelopment
-----------

[](#development)

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

License
-------

[](#license)

MIT

###  Health Score

41

—

FairBetter than 89% of packages

Maintenance89

Actively maintained with recent releases

Popularity10

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity50

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~2 days

Total

6

Last Release

56d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/cfd7d24cbbf32400fa13ce0bbe7a31edd2d66a6d4488eafdb3d64c5337bf0435?d=identicon)[philiprehberger](/maintainers/philiprehberger)

---

Top Contributors

[![philiprehberger](https://avatars.githubusercontent.com/u/8218077?v=4)](https://github.com/philiprehberger "philiprehberger (20 commits)")

---

Tags

laraveldatabasesqlitesqldateexpressions

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StyleLaravel Pint

### Embed Badge

![Health badge](/badges/philiprehberger-laravel-db-expressions/health.svg)

```
[![Health](https://phpackages.com/badges/philiprehberger-laravel-db-expressions/health.svg)](https://phpackages.com/packages/philiprehberger-laravel-db-expressions)
```

###  Alternatives

[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[eusonlito/laravel-database-cache

Cache Database Query results on Laravel Query Builder or Eloquent

194.2k](/packages/eusonlito-laravel-database-cache)[toponepercent/baum

Baum is an implementation of the Nested Set pattern for Eloquent models.

3154.7k](/packages/toponepercent-baum)[bigsinoos/j-eloquent

Convert eloquent date attributes to jalali (Persian) dates on the fly. (supports model propery access, toJson, toString and toArray).

224.7k](/packages/bigsinoos-j-eloquent)[calebdw/laravel-sql-entities

Manage SQL entities in Laravel with ease.

301.3k](/packages/calebdw-laravel-sql-entities)[ramadan/easy-model

A Laravel package for enjoyably managing database queries.

101.6k](/packages/ramadan-easy-model)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)