Timestamps?

[ncbraner]

Timestamps, How do we get these to work? to write the raw sql in the db tools we need to type hint or use the to_timestamp methods, but how do we do it from within Laravel? if i set the format, i get errors not being able to parse all of the strings. if i dont, I cant get the milliseconds. any examples out there?

[yajra]

Can you provide a snippet of your use case? But for dates, the package follows Y-m-d H:i:s format. Or you can use a Carbon instance value, which should work as expected.

[ncbraner]

We updated the table from a date to at imestamp
we do have code that runs without error
when we call $model::create($row) we are supplying the timestamp
"created_at": "2024-04-10 00:42:29.321 +00:00", "created_by": "1195",

we have an observer that changes it to a datetime string
$dateTime = Carbon::parse($groupsStg->created_at); $dateTimeString = $dateTime->toDateTimeString(); $groupsStg->created_at = $dateTimeString;

But we loose the fractional seconds.

To try and get those we update our observer to
$dateTime = Carbon::parse($groupsStg->created_at); $dateTimeString = $dateTime->format('Y-m-d H:i:s.u'); // 'u' includes microseconds $groupsStg->created_at = $dateTimeString;

And we changed the models date format
protected $dateFormat = 'Y-m-d H:i:s.u';

this produces a error of Error Message : ORA-01830: date format picture ends before converting entire input string

To make this work in direct raw query on the db i need to type the string for example
TIMESTAMP '2024-04-10 00:49:53.321'

or i could use the TO_TIMESTAMP of course if i wanted to change the format. if we do not do those we get the same error that we get from the app.

how do i get these to post through from code though?

[yajra]

Oic, we might need to update the default NLS DATE FORMAT to include the microseconds.

Atm, maybe try using DB::setDateFormat('YYYY-MM-DD HH24:MI:SS.FF')

[ncbraner]

I did attempt that as well. and also in the database config 'session_vars' => [ 'NLS_TIME_FORMAT' => 'HH24:MI:SSXFF', 'NLS_DATE_FORMAT' => 'YYYY-MM-DD HH24:MI:SS.FF', 'NLS_TIMESTAMP_FORMAT' => 'YYYY-MM-DD HH24:MI:SS.FF', ],
Lets keep this chat going. For now I was able to get it with a raw query. which isnt ideal

        `public static function create(array $attributes = [])
{
    $model = new static($attributes);

    // Fire the creating event
    if ($model->fireModelEvent('creating') === false) {
        return false;
    }

    // Extract the table name from the model
    $table = $model->getTable();

    // Extract the attributes and their values
    $columns = array_keys($attributes);
    $values = array_values($attributes);

    // Format the timestamps correctly and handle null values
    foreach ($columns as $index => $column) {
        if (in_array($column, ['created_at', 'updated_at', 'deleted_at'])) {
            // Always use the current timestamp
            $timestamp = Carbon::now();

            if ($column == 'created_at') {
                $values[$index] = "TO_TIMESTAMP('" . addslashes($timestamp->format('Y-m-d H:i:s.u')) . "', 'YYYY-MM-DD HH24:MI:SS.FF6')";
            } else {
                $values[$index] = "TO_DATE('" . addslashes($timestamp->format('Y-m-d H:i:s')) . "', 'YYYY-MM-DD HH24:MI:SS')";
            }
        } else {
            $values[$index] = $values[$index] === null ? 'NULL' : DB::getPdo()->quote($values[$index]);
        }
    }

    // Construct the SQL INSERT statement
    $columnsString = implode(', ', $columns);
    $valuesString = implode(', ', $values);

    $sql = "INSERT INTO $table ($columnsString) VALUES ($valuesString)";

    // Execute the raw SQL query
    DB::statement($sql);

    // Fire the created event
    $model->fireModelEvent('created', false);

    // Return an instance of the model
    return $model;`

The key to this was the timestamp $values[$index] = "TO_TIMESTAMP('" . addslashes($timestamp->format('Y-m-d H:i:s.u')) . "', 'YYYY-MM-DD HH24:MI:SS.FF6')";