I almost messed up timestamptz in Laravel
Why use timestamptz columns instead of timestamp?
If you’re using Postgres, it’s recommended. Check out the very informative “Don’ t Do This” page in the Postgres wik (https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29).
Laravel supports timestamptz columns in migrations via
However, if that is all you do, your data may silently be stored incorrectly (due to exclusion of the timezone offset in queries) if:
- One of your database server(s) are not set to the same timezone as your application.
- One of your model’s Carbon objects are not set to the Application’s timezone. For example,
Carbon::now()are not cast to the same string unless Laravel is set to UTC, which is the default. Even then, a Carbon object’s timezone is neither immutable nor limited to Laravel’s timezone.
To ensure the timestamp is stored correctly, define
$casts for each attribute to datetime and define
$dateFormat = 'Y-m-d H:i:s P'; on each model.
class Example extends Model
protected $dateFormat = 'Y-m-d H:i:s P'; protected $casts = [
'created_at' => 'datetime',
// 'updated_at' => 'datetime',
Neither can be omitted! In the above, the
created_at attribute will be stored correctly with timezone offset, but
updated_at will not.
These two columns, generated by
$table->timestampsTz();, are why we cannot use a custom Cast class (https://laravel.com/docs/8.x/eloquent-mutators#custom-casts) to solve our problem.
Upon creating and saving a new model instance,
updated_at are not yet set. When Laravel generates these, in the absence of
$dateFormat being defined, the Cast setter is passed
$value of format
Additionally, we cannot access the underlying Carbon object in the Cast setter, as those two model attributes are not yet set by time of the Cast setter call.
So, make sure you define
$dateFormat = 'Y-m-d H:i:s P'; and define datetime
$casts for all such attributes in your models if you’re using timestamptz!