Changing the type of last_activity column when using database session storage in Laravel 5

Question

I've just started using the

database

session driver in Laravel 5.5 along with PostgreSQL, and I've ran into a slight inconvenience.

I would like to keep the

last_activity

column as

timestamp with timezone

, but Laravel wants to write an integer into it, and it sometimes also attempts to delete from it based on an integer value.

I tried doing the following in my

Session

model:

public function setLastActivityAttribute($ts){

    $this->attributes['last_activity'] = date('c', $ts);

}

which works well for saving, but when Laravel tries to garbage collect the sessions it uses an integer value which causes a

PDOException

:

SQLSTATE[22008]: Datetime field overflow: 7 ERROR: date/time field value out of range: "1506794381"

HINT: Perhaps you need a different "datestyle" setting. (SQL: delete from "sessions" where "last_activity" <= 1506794381)

Is there any way I could specify a format or intercept the process to keep the

timestamptz

column type?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I suggest creating a custom session driver by overwriting the gc and expired methods of the DatabaseSessionHandler.php driver to work with timestamp with timezone instead of integers.

The gc currently passes time() - $lifetime but you can change this to date('c', time() - $lifetime). And in expired you can call strtotime on $session->last_activity to convert to the same unit as getTimestamp().

<?php

namespace App\Extensions;
use Illuminate\Session\DatabaseSessionHandler;

class MyPostgresHandler extends DatabaseSessionHandler
{   
    public function gc($lifetime) {
        $sessions = $this->getQuery()->where('last_activity', '<=', date('c', time() - $lifetime))->get();
        foreach ($sessions as $session) {
            $this->destroy($session->id);
        }
    }

    protected function expired($session)
    {
        return isset($session->last_activity) &&
            strtotime($session->last_activity) < Carbon::now()->subMinutes($this->minutes)->getTimestamp();
    }
}

Then you can register your new driver by extending ServiceProvider

<?php

namespace App\Providers;

use App\Extensions\MyPostgresHandler;
use Illuminate\Support\Facades\Session;
use Illuminate\Support\ServiceProvider;

class SessionServiceProvider extends ServiceProvider
{
    /**
     * Perform post-registration booting of services.
     *
     * @return void
     */
    public function boot()
    {
        Session::extend('my-db-driver', function ($app) {
            return new MyPostgresHandler;
        });
    }

    /**
     * Register bindings in the container.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Now you can use my-db-driver in config/session.php

For more info see https://laravel.com/docs/5.5/session#implementing-the-driver

Changing the type of last_activity column when using database session storage in Laravel 5

Sign up