Laravel Carbon allow trailing data at Postgresql Timestamp

Question

I have some timestamp records on the DB that have trailing milliseconds at the timestamp and some not have. How to allowing that trailing data (millisecond) in carbon? Is this possible?

Here's the sample of my data

I can't always change the data manually because there are some other services using the same database and sometimes storing timestamp with trailing milliseconds.

Knighthood

Please post some example timestamps – Shudder

I've edit my question to add some example timestamps – Knighthood

only this two formats in the database or any other formats ?? – Nashbar

yes, just this two formats in the db @Nashbar – Knighthood

Answer 1

As you are using Postgres, your timestamp probably has TIME WITH TIMEZONE

Example: "2018-04-19 07:01:19.929554".

In Such case have to add a date Mutator to your Model.

In your Model add this field for date mutator:

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

Alternate Solution:

As you have a mix of timestamps with and without milliseconds I suggest you try this solution using Laravel field mutators:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * Parse the created at field which can optionally have a millisecond data.
     *
     * @param  string  $created_at
     * @return Carbon::Object
     */
    public function getCreatedAtAttribute($created_at)
    {
            // Try to remove substring after last dot(.), removes milliseconds
            $temp = explode('.', $created_at);

            // If created_at had milliseconds the array count would be 2
            if(count($temp) == 2) {
                unset($temp[count($temp) - 1]); // remove the millisecond part
            } else {
                $temp = [$created_at]; // created_at didnt have milliseconds set it back to original
            }

            return Carbon::parse(implode('.', $temp))->format('Y-m-d H:i:s')
    }
}

Shudder

No, my timestamp type in Postgres is TIME WITHOUT TIMEZONE. It's just issue with some timestamp have a trailing millisecond, and Carbon seems can't deal with that – Knighthood

@Knighthood I've added an alternate solution to compensate for varying types of date fields – Shudder

Well thats a workaround, is there another way that is more genuine? – Knighthood

@Knighthood consistency is assumed in a data type with specific formats (like date here). If you have mixed format dates in your database then the best solution is to fix the problem at this level. Port existing in-consistencies to a normalized format and make sure future additions to the column are the same format. Laravel user carbon and it cannot magically parse all date formats. – Shudder

Answer 2

I have observed in Laravel, storing datetime in database ignores millisecond field, probably depends on version and db server type.

Also Laravel has whereData() and whereTime() query builders but we need something like whereDateTimeTz() in all cases.

I recommend storing datetime as unix timestamps in database.

From user timezone convert to GMT and save it to db as millis-timestamp

Carbon::parse('date', 'user_timezone')->setTimezone('GMT')->getPreciseTimestamp(3);

While displaying just convert the db timestamp (GMT) back to user timezone including DST status.

Kimberli

actually i can't change the datetime format in database :( – Knighthood

what about adding additional column in table as updated_at_timestamps? After few months you may drop the updated_at. You can add onCreate onUpdate event listeners to model and update new column as timestamp. – Extracanonical

I can't add additional column to the table too. onUpdate listeners are triggered from laravel itself but the change is not from one service only, so if the other service updates the column and causing trailing milliseconds, it won't be triggered. – Knighthood

Answer 3

Laravel 7 provides better date parsing by falling back to Carbon::parse if the recevied timestamp from the database doesn't match the expected $dateFormat.

PR: https://github.com/laravel/framework/pull/30628

Randers