timestampTz fields in Laravel

Question

Laravel 5.4 supports the Postgres TIMESTAMP WITH TIME ZONE field type in migrations:

$table->timestampTz('scheduled_for');

Laravel can be set up to convert date fields (DATE, DATETIME, TIMESTAMP) into Carbon objects (and does so by default for the created_at and updated_at TIMESTAMP fields), but putting scheduled_for into the $dates field causes an error with the timezone-aware version:

InvalidArgumentException with message 'Trailing data'

Looking in the database and tinker, the field's value appears to be something like 2017-06-19 19:19:19-04. Is there a native way to get a Carbon object out of one of these field types? Or am I stuck using an accessor?

Abuse

Answer 1

Resurrecting this question, hopefully with a helpful answer that gets accepted.

Laravel assumes a Y-m-d H:i:s database timestamp format. If you're using a Postgres timestampz column, that's obviously different. You need to tell Eloquent how to get Carbon to parse that format.

Simply define the $dateFormat property on your model like so:

Class MyModel extends Eloquent {

    protected $dateFormat = 'Y-m-d H:i:sO';

}

Credit where credit is due: I found this solution in a GitHub issue

Lagomorph

Neato, that's elegant! – Abuse

What if I have both timestamp and timestampTz column types on the same table/Model/? – Hedley

@Hedley It's been a while since I've head to deal with this -- but I assume you'll have to write something a little more extensive that involves making a hash that holds the column -> format mapping and then override the method that is looking up the $dateFormat and perform the lookup on your own, then return the correct/desired. – Lagomorph

Thanks for your reply, @Jim. I ended up simply storing the timezone in a different field but I'll bear this in mind. – Hedley

Answer 2

Put this inside your model

protected $casts = [
    'scheduled_for' => 'datetime'   // date | datetime | timestamp
];

Using $dates is more likely obsolete as $casts do the same stuff (maybe except $dateFormat attribute which can work only for $dates fields iirc, but I saw some complaining on it)

Edit

I was testing Carbon once on Laravel 5.4 and I created a trait for it

this is not production level code yet so include it in your model on your own risk

<?php namespace App\Traits;

use Carbon\Carbon;

trait castTrait
{
    protected function castAttribute($key, $value)
    {
        $database_format        = 'Y-m-d H:i:se';   // Store this somewhere in config files
        $output_format_date     = 'd/m/Y';          // Store this somewhere in config files
        $output_format_datetime = 'd/m/Y H:i:s';    // Store this somewhere in config files

        if (is_null($value)) {
            return $value;
        }

        switch ($this->getCastType($key)) {
            case 'int':
            case 'integer':
                return (int) $value;
            case 'real':
            case 'float':
            case 'double':
                return (float) $value;
            case 'string':
                return (string) $value;
            case 'bool':
            case 'boolean':
                return (bool) $value;
            case 'object':
                return $this->fromJson($value, true);
            case 'array':
            case 'json':
                return $this->fromJson($value);
            case 'collection':
                return new BaseCollection($this->fromJson($value));
            case 'date':
                Carbon::setToStringFormat($output_format_date);
                $date = (string)$this->asDate($value);
                Carbon::resetToStringFormat();  // Just for sure
                return $date;
            case 'datetime':
                Carbon::setToStringFormat($output_format_datetime);
                $datetime = (string)$this->asDateTime($value);
                Carbon::resetToStringFormat();
                return $datetime;
            case 'timestamp':
                return $this->asTimestamp($value);
            default:
                return $value;
        }
    }

    /**
     * Return a timestamp as DateTime object with time set to 00:00:00.
     *
     * @param  mixed  $value
     * @return \Carbon\Carbon
     */
    protected function asDate($value)
    {
        return $this->asDateTime($value)->startOfDay();
    }

    /**
     * Return a timestamp as DateTime object.
     *
     * @param  mixed  $value
     * @return \Carbon\Carbon
     */
    protected function asDateTime($value)
    {
        $carbon = null;
        $database_format = [ // This variable should also be in config file
            'datetime'  => 'Y-m-d H:i:se',      // e -timezone
            'date'      => 'Y-m-d'
        ];

        if(empty($value)) {
            return null;
        }

        // If this value is already a Carbon instance, we shall just return it as is.
        // This prevents us having to re-instantiate a Carbon instance when we know
        // it already is one, which wouldn't be fulfilled by the DateTime check.
        if ($value instanceof Carbon) {
            $carbon = $value;
        }

         // If the value is already a DateTime instance, we will just skip the rest of
         // these checks since they will be a waste of time, and hinder performance
         // when checking the field. We will just return the DateTime right away.
        if ($value instanceof DateTimeInterface) {
            $carbon = new Carbon(
                $value->format($database_format['datetime'], $value->getTimezone())
            );
        }

        // If this value is an integer, we will assume it is a UNIX timestamp's value
        // and format a Carbon object from this timestamp. This allows flexibility
        // when defining your date fields as they might be UNIX timestamps here.
        if (is_numeric($value)) {
            $carbon = Carbon::createFromTimestamp($value);
        }

        // If the value is in simply year, month, day format, we will instantiate the
        // Carbon instances from that format. Again, this provides for simple date
        // fields on the database, while still supporting Carbonized conversion.
        if ($this->isStandardDateFormat($value)) {
            $carbon = Carbon::createFromFormat($database_format['date'], $value)->startOfDay();
        }

        // Finally, we will just assume this date is in the format used by default on
        // the database connection and use that format to create the Carbon object
        // that is returned back out to the developers after we convert it here.
        $carbon = Carbon::createFromFormat(
            $database_format['datetime'], $value
        );

        return $carbon;
    }
}

Folkestone

$casts doesn't appear to work. It still appears to be running it through Carbon (unsuccessfully). – Abuse

The reason is wrong format that comes from database. Laravel is trying to parse your input datetime string using incorrect format mask – Eveevection

Given that Laravel supports timestampTz in migrations, it can't really be called the "wrong" format. Thus far it appears Laravel doesn't fully support it, hence my question - it seems odd to implement it in migrations without it being particularly usable in the actual code. – Abuse

I updated my answer, you can test the trait I provided – Eveevection

I'm leaning towards just using a timestamp field and storing scheduled_tz alongside it as a string, allowing me to do an accessor like public function getScheduledForAttribute($value) { return Carbon::parse($value)->setTimezone($this->scheduled_tz); } – Abuse

My solution do this, but you will not need to declare attribute functions, so it's even better – Eveevection