Doctrine DBAL: Updating timestamp field with 'NOW()' value

Question

Using Doctrine DBAL, I have some code that inserts a new row into the main database from a form values binded as $telephone_international and $surname.

After that is done, it inserts a new record into a duplicate database. $app['dbs']['backup']

If that's successful, the entry inserted previously the main database gets its copied value updated. The copied column is a timestamp, default value is 0, but the following code should change it to the current time.

$app['dbs']['main']->update('phonebook', array(
    'mediated'  => 'NOW()'
), array(
    'telephone' => $telephone_international, 
    'surname'   => $surname
));

But the value is still 0000-00-00 00:00:00. I wonder if 'NOW()' is being treated as a string.

as simple workaround you could use php's date functions like: 'mediated' => time(), to insert a timestamp — ivoba, Jun 29 '12 at 09:15

score 1 · Accepted Answer · answered Jun 30 '12 at 12:15

1

As i proposed in the comments above, this seems the way to go, as stated here:
http://doctrine-orm.readthedocs.org/en/2.0.x/cookbook/working-with-datetime.html

So try like this:

$app['dbs']['main']->update('phonebook', array(
    'mediated'  => new \DateTime("now")
), array(
    'telephone' => $telephone_international, 
    'surname'   => $surname
));

answered Jun 30 '12 at 12:15

ivoba

5,780
5
48
55

Thanks for the answer. However, I (for should I say, my co-developer) decided to use `date_default_timezone_set('GMT');` and `'mediated' => date('Y-m-d H:i:s');` to achieve the desired result. I will check to see if this does work in some other app, at which point, I'll mark this as accepted. – Adam Elsodaney Jul 13 '12 at 16:30
1

Now that I've returned to Symfony2 and Doctrine development, I thought I'd revisit this question. The solution would be to use `$now = new \DateTime('NOW', new \DateTimeZone('UTC'));` then `'mediated' => $now->format('Y-m-d H:i:s');` to get the current UTC time. I'll acccept this answer since it pointed me in the right direction, but the code in your answer will fail since `DateTime("now")` should be a string, not an object (unless I choose to store it as an object) – Adam Elsodaney Oct 17 '12 at 17:20
@AdamElsodaney Yes I would have thought the dbal layer would have accepted a `DateTime` object here but I indeed had to format my `DateTime` to `(new \DateTime())->format('Y-m-d H:i:s')` as you said. – jcroll Nov 26 '14 at 15:50

score 0 · Answer 2 · answered May 15 '14 at 19:53

I stumbled over the same problem. As there's no much and no good documentation for DBAL itself, I'm going to post my solution.

There's a last parameter which specifies the type (in the order of the data and identifcation array merged; as if they were in the same array):

$app['dbs']['main']->update('phonebook', array(
    'mediated'  => new DateTime()
), array(
    'telephone' => $telephone_international, 
    'surname'   => $surname
), array(
    'datetime',
    PDO::PARAM_STR,
    PDO::PARAM_STR
));

Doctrine DBAL: Updating timestamp field with 'NOW()' value

2 Answers2

Linked