Insert with functions

[shopblocks]

Im trying to an insert with the mysql function NOW() for a timestamp, but i get incorrect datetime value 'NOW()' which makes me think that it is sending it through as a string. Is there a way to get it to send through the mysql function, or do i need to do a php date() method?

[dereuromark]

Any update here from the team?

[MasterOdin]

@dereuromark I can confirm that this is a problem and I'd like to make a patch to fix it using the Phinx\Util\Literal class that solves this problem for column creation.

For the implementation, do you want it to be allowed to do variable number of Literals per statement or assume that all inserts will use the same Literals in the same place.

For example, should the following two examples be allowed:

$this->table('test')->insert([
    ['id' => 1, 'test_1' => '2019-01-01 12:59:59', 'test_2' => Literal::from('NOW()')],
])->update();

$this->table('test')->insert([
    ['id' => 1, 'test_1' => '2019-01-01 12:59:59', 'test_2' => Literal::from('NOW()')],
    ['id' => 2, 'test_1' => '2019-01-01 12:59:59', 'test_2' => '2019-02-01 12:59:59']
])->update();

or is the expectation going to always be to do:

$this->table('test')->insert([
    ['id' => 1, 'test_1' => '2019-01-01 12:59:59', 'test_2' => Literal::from('NOW()')],
    ['id' => 2, 'test_1' => '2019-01-01 12:59:59' 'test_2' => Literal::from('NOW()')]
])->update();

and the first example above would end up looking like:

$this->table('test')->insert([
    ['id' => 1, 'test_1' => '2019-01-01 12:59:59', 'test_2' => Literal::from('NOW()')],
])->update();

$this->table('test')->insert(
    ['id' => 2, 'test_1' => Literal::from('NOW()'), 'test_2' => '2019-02-01 12:59:59']
])->update();

As allowing the first two examples will affect efficiency somewhat for bulk inserts (as have to separate out any rows that have Literals and run them individually instead of the current approach of running all inserts in a single prepared query, executing with different parameters.