How do I make doctrine support timestamp columns?

Question

I'm trying to apply the following migration:

Schema::table('users', function (Blueprint $table) {
    $table->timestamp('created_at')->useCurrent()->change();
});

But artisan says:

  [Doctrine\DBAL\DBALException]
  Unknown column type "timestamp" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL
  \Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). I
  f this error occurs during database introspection then you might have forgot to register all database types for a
  Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMapp
  edDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping inform
  ation.

When I try to install mmerian/doctrine-timestamp (composer install mmerian/doctrine-timestamp), composer says:

  [InvalidArgumentException]
  Could not find package mmerian/doctrine-timestamp at any version for your minimum-stability (stable). Check the pa
  ckage spelling or your minimum-stability

What do I do?

UPD With composer require mmerian/doctrine-timestamp=dev-master, I was able to install the package, then added Type::addType('timestamp', 'DoctrineTimestamp\DBAL\Types\Timestamp'); before Schema::table statement, but now I've got the other error:

  [Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_at' (SQL: ALTER TABLE u
  sers CHANGE created_at created_at INT DEFAULT 'CURRENT_TIMESTAMP' NOT NULL)

UPD I checked again if it works with mmerian/doctrine-timestamp, since I added only first of the lines from the docs back then (or the doc was updated):

Type::addType('timestamp', 'DoctrineTimestamp\DBAL\Types\Timestamp');                                          
DB::getDoctrineConnection()->getDatabasePlatform()->registerDoctrineTypeMapping('Timestamp', 'timestamp');

But it doesn't help as well. The migration succeeds, but the column definition doesn't change.

You can manually add https://github.com/mmerian/doctrine-timestamp/blob/master/lib/DoctrineTimestamp/DBAL/Types/Timestamp.php to your project — Alex Tartan, Jan 13 '16 at 19:28

score 11 · Answer 1 · answered Jul 14 '22 at 15:07

With the current Laravel version you just need to add the following to your config/database.php and changing timestamp columns in migrations will work.

use Illuminate\Database\DBAL\TimestampType;
 
'dbal' => [
    'types' => [
        'timestamp' => TimestampType::class,
    ],
],

Source

x-yuri · Accepted Answer · 2016-01-14T09:20:36.463

As one can see, mmerian/doctrine-timestamp doesn't solve the issue. First, after this line $table->getColumns()['created_at'] is

class Doctrine\DBAL\Schema\Column#520 (16) {
  protected $_type => class Doctrine\DBAL\Types\DateTimeType#504 (0) { }
  protected $_length => NULL
  protected $_precision => int(10)
  protected $_scale => int(0)
  protected $_unsigned => bool(false)
  protected $_fixed => bool(false)
  protected $_notnull => bool(true)
  protected $_default => string(17) "CURRENT_TIMESTAMP"
  protected $_autoincrement => bool(false)
  protected $_platformOptions => array(0) { }
  protected $_columnDefinition => NULL
  protected $_comment => NULL
  protected $_customSchemaOptions => array(0) { }
  protected $_name => string(10) "created_at"
  protected $_namespace => NULL
  protected $_quoted => bool(false)
}

and $this->getTableWithColumnChanges($blueprint, $table)->getColumns()['created_at'] is

class Doctrine\DBAL\Schema\Column#533 (16) {
  protected $_type => class DoctrineTimestamp\DBAL\Types\Timestamp#513 (0) { }
  protected $_length => NULL
  protected $_precision => int(10)
  protected $_scale => int(0)
  protected $_unsigned => bool(false)
  protected $_fixed => bool(false)
  protected $_notnull => bool(true)
  protected $_default => string(17) "CURRENT_TIMESTAMP"
  protected $_autoincrement => bool(false)
  protected $_platformOptions => array(0) { }
  protected $_columnDefinition => NULL
  protected $_comment => NULL
  protected $_customSchemaOptions => array(0) { }
  protected $_name => string(10) "created_at"
  protected $_namespace => NULL
  protected $_quoted => bool(false)
}

So, first I can't see information about ON UPDATE part here. Second, the onle difference is $_type value. What I can confirm after this line, $tableDiff->changedColumns['created_at']->changedProperties is

array(1) {
  [0] => string(4) "type"
}

Then, when generating ALTER TABLE statement, it all comes down to this

public function getDefaultValueDeclarationSQL($field)
{
    $default = empty($field['notnull']) ? ' DEFAULT NULL' : '';
    if (isset($field['default'])) {
        $default = " DEFAULT '".$field['default']."'";
        if (isset($field['type'])) {
            if (in_array((string) $field['type'], array("Integer", "BigInt", "SmallInt"))) {
                $default = " DEFAULT ".$field['default'];
            } elseif (in_array((string) $field['type'], array('DateTime', 'DateTimeTz')) && $field['default'] == $this->getCurrentTimestampSQL()) {
                $default = " DEFAULT ".$this->getCurrentTimestampSQL();
            } elseif ((string) $field['type'] == 'Time' && $field['default'] == $this->getCurrentTimeSQL()) {
                $default = " DEFAULT ".$this->getCurrentTimeSQL();
            } elseif ((string) $field['type'] == 'Date' && $field['default'] == $this->getCurrentDateSQL()) {
                $default = " DEFAULT ".$this->getCurrentDateSQL();
            } elseif ((string) $field['type'] == 'Boolean') {
                $default = " DEFAULT '" . $this->convertBooleans($field['default']) . "'";
            }
        }
    }
    return $default;
}

Somewhere around this line there supposed to be a check for Timestamp type to turn 'CURRENT_TIMESTAMP' into CURRENT_TIMESTAMP. Is this possible within mmerian/doctrine-timestamp? That question is left open for now. This check would most likely solve my particular issue. But for now I'm going to get away with this:

DB::statement('ALTER TABLE users MODIFY COLUMN created_at
    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP');

It's 2019 and this is still a problem. I would be happy enough if I could say `$table->dateTime('happening_at')->useCurrent();` OR `$table->dateTime('happening_at')->default(DB::raw('CURRENT_TIMESTAMP');`. But Laravel doesn't support CURRENT_TIMESTAMP for DateTime columns, even though MySQL has for a while. Thanks for the raw statement suggestion, it's what I'll have to do. — Soulriser, Apr 17 '19 at 17:43
and to reverse, `DB::statement('ALTER TABLE users MODIFY COLUMN created_at TIMESTAMP NULL DEFAULT NULL');` for example. — luminol, Jun 09 '19 at 01:11

score 7 · Answer 3 · answered Jul 13 '17 at 09:50

7

hi~ you can use "datetime" type:

 Schema::table('orders', function ($table) {

        $table->datetime('pay_time')->nullable()->change();

    });

answered Jul 13 '17 at 09:50

徐正洋

103
1
2

3

This won't allow you to use timestamp features such as useCurrent(). – jpswade Oct 13 '19 at 15:29
This won't allow the timestamp column to be altered to add Nullable – Peter Fox Jan 11 '21 at 10:15

score 1 · Answer 4 · answered Oct 12 '21 at 10:41

if you want to make migration for current timestamp and get the error "Unknown column type "timestamp" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType()" then use like this

\DB::statement("ALTER TABLE `order_status_logs` CHANGE `created_at` `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP");

score 0 · Answer 5 · answered Jan 13 '16 at 19:31

0

Set minimum-stability setting to dev in your composer.json, because mmerian/doctrine-timestamp has only dev-master version, to example:

{
    "minimum-stability": "dev",
    "require": {
        ...
     }
}

Then, when bootstraping your doctrine connection:

Type::addType('timestamp', 'DoctrineTimestamp\DBAL\Types\Timestamp');
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('Timestamp', 'timestamp');

answered Jan 13 '16 at 19:31

maximkou

5,252
1
20
41

Lowering minimum stability is [not recommended](https://igor.io/2013/02/07/composer-stability-flags.html). Running `composer install mmerian/doctrine-timestamp=dev-master` does the trick. – x-yuri Jan 13 '16 at 20:40
Adding the second line didn't help. – x-yuri Nov 28 '16 at 08:51
@x-yuri thanks for pointing me in the right direction. I had to use `composer require mmerian/doctrine-timestamp=dev-master` instead though. – Second2None Nov 17 '19 at 11:16

score 0 · Answer 6 · answered Nov 24 '16 at 16:24

0

I builded this for it, since Doctrine does not want to support it cause it's a MySQL-specific column type.

answered Nov 24 '16 at 16:24

Mark Topper

652
1
8
23

It doesn't work for me. The migration succeeds, but column definition stay the same: `created_at timestamp NULL DEFAULT NULL`. – x-yuri Nov 28 '16 at 08:42
What are you trying to change it too? – Mark Topper Nov 28 '16 at 08:59
`ALTER TABLE users MODIFY COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP`, see my answer – x-yuri Nov 28 '16 at 09:07
What's the migration code you uses for this, I will go ahead and try it out and figure out how to fix this. – Mark Topper Nov 28 '16 at 09:23
How about `composer install mmerian/doctrine-timestamp=dev-master`, did that do the trick? – Mark Topper Nov 28 '16 at 09:25
The answers are all on the page, [here](http://stackoverflow.com/questions/34774628/how-do-i-make-doctrine-support-timestamp-columns/40791069?noredirect=1#comment68897630_34775435) and in the question. Create a table with `created_at` column (`$table->timestamps();`), and try to change default value (`$table->timestamp('created_at')->useCurrent()->change();`). – x-yuri Nov 28 '16 at 09:40
What is the current layout of the `created_at` column? – Mark Topper Nov 28 '16 at 09:59
`created_at timestamp NULL DEFAULT NULL` – x-yuri Nov 28 '16 at 13:01

score 0 · Answer 7 · answered Oct 06 '22 at 12:19

To prevent adding Dbal using on app config I am using this in my migration class.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class UpdateSlidePropsonSlidesTable extends Migration
{

    public function __construct()
    {
        // ! This is to allow changing timestamps without forcing require dbal on non dev composer.
        \Doctrine\DBAL\Types\Type::addType(
            'timestamp',
            \Illuminate\Database\DBAL\TimestampType::class
        );
    }

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('slides', function (Blueprint $table) {
            $table->renameColumn('date_start', 'publish_at');
            $table->renameColumn('date_end', 'unpublish_at');
        });
        Schema::table('slides', function (Blueprint $table) {
            $table->timestamp('publish_at')->nullable()
                ->change()
                ->comment('The date where the console published state should be changed to published.');
            $table->timestamp('unpublish_at')->nullable()
                ->change()
                ->comment('The date where the console published state should be changed to archived.');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('slides', function (Blueprint $table) {
            $table->renameColumn('publish_at', 'date_start');
            $table->renameColumn('unpublish_at', 'date_end');
        });
    }
}

I get a new exception using your method, ```Doctrine\DBAL\Exception``` ```Type timestamp already exists.``` — Hooman Limouee, Jan 11 '23 at 17:33
check config cache is cleared, and you dont have the DBal type imported from config — Kwaadpepper, Jan 12 '23 at 13:58

How do I make doctrine support timestamp columns?

7 Answers7

Linked

Related