laravelpostgresqleloquentphp-carbonsql-timestamp

Right way to save timestamps to database in laravel?


As part of a standard laravel application with a vuejs and axios front-end, when I try to save an ISO8601 value to the action_at field, I get an exception.

class Thing extends Model {
    protected $table = 'things';
    // timestamp columns in postgres
    protected $dates = ['action_at', 'created_at', 'updated_at'];
    protected $fillable = ['action_at'];
}

class ThingController extends Controller {
    public function store(Request $request) {
        $data = $request->validate([
            'action_at' => 'nullable',
        ]);
        // throws \Carbon\Exceptions\InvalidFormatException(code: 0): Unexpected data found.
        $thing = Thing::create($data);
    }
}

My primary requirement is that the database saves exactly what time the client thinks it saved. If another process decides to act on the "action_at" column, it should not be a few hours off because of timezones.

I can change the laravel code or I can pick a different time format to send to Laravel. What's the correct laravel way to solve this?


Solution

  • class Thing extends Model {
        protected $table = 'things';
        // timestamp columns in postgres
        protected $dates = ['action_at', 'created_at', 'updated_at'];
        protected $fillable = ['action_at'];
    }
    
    class ThingController extends Controller {
        public function store(Request $request) {
            $data = $request->validate([
                'action_at' => 'nullable',
            ]);
            // convert ISO8601 value, if not null
            if ($data['action_at'] ?? null && is_string($data['action_at'])) {
                // note that if the user passes something not in IS08601
                // it is possible that Carbon will accept it
                // but it might not be what the user expected.
                $action_at = \Carbon\Carbon::parse($data['action_at'])
                    // default value from config files: 'UTC'
                    ->setTimezone(config('app.timezone'));
                // Postgres timestamp column format
                $data['action_at'] = $action_at->format('Y-m-d H:i:s');
            }
    
            $thing = Thing::create($data);
        }
    }
    

    Other tips: don't use the postgres timestamptz column if you want to use it with protected $dates, laravel doesn't know how to give it to carbon the way postgres returns the extra timezone data.

    See the carbon docs for other things you can do with the $action_at instance of \Carbon\Carbon, such as making sure the date is not too far in the future or too far in the past. https://carbon.nesbot.com/docs/