0

I need to convert the following query that works against a database table

"UPDATE MYTABLE SET field_temp= %s", UNIX_TIMESTAMP(field);

into Drupal language

db_update('MYTABLE')
      ->fields(array(
          'field_temp' => UNIX_TIMESTAMP('field'), //or strtotime()
         ))
        ->execute();

seems to be not working.

UPDATE

db_update('MYTABLE')
        ->expression('field_temp',  "UNIX_TIMESTAMP('field')"),
        ->execute();

will run into:

PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: 

Some more details:

  • 'field' is datetime format type, field_type is 'int' format type
  • I am running this script through a hook_update_N
  • I am debugging it by running update.php
2
  • As a tip for debugging, running this code will have given you a white screen, possibly with an error message depending on your server configuration. If there was no error message you could still check your web server error logs for any errors and will have had an error about a call to undefined function UNIX_TIMESTAMP(), which would give you some direction on where to start looking for the problem.
    – rooby
    Commented Sep 16, 2013 at 14:53
  • What is the incorrect date time value in the error message? It should come right after the part you pasted in here. The more complete the error messages the better. The date should be in the form "YYYY-MM-DD HH:MM:SS" if it is a datetime, which should be acceptable for the UNIX_TIMESTAMP() function. - Is 'field' the name of a database field or is it pseudo code? If it is a db field it should be UNIX_TIMESTAMP(field), without the quotes. The quotes makes it a string, not a field.
    – rooby
    Commented Sep 16, 2013 at 15:30

1 Answer 1

2

You need to add an expression():

db_update('MYTABLE')
  ->expression('field_temp', "UNIX_TIMESTAMP(field)")
  ->execute();
5
  • 1
    Yeah also, the code in the question is trying to use a php function called UNIX_TIMESTAMP() as opposed to a mysql function. Note the quotes.
    – rooby
    Commented Sep 16, 2013 at 14:50
  • You should use a dynamic query if you need more complex queries, dynamic query generation or alterability. For simple queries, static queries ("db_query()") is the best choice
    – Enxebre
    Commented Sep 16, 2013 at 14:54
  • @Enxebre I'm afraid not, you should ALWAYS use a db_update() for an update query, unless you physically can't reproduce the query with Drupal's Api. From the db_query() docs: Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete() respectively.
    – Clive
    Commented Sep 16, 2013 at 14:57
  • 1
    Thank you @Clive, you are right. The info that I am giving is only refered to Select queries.
    – Enxebre
    Commented Sep 16, 2013 at 15:08
  • 1
    If 'field' is another db field it should be minus the internal quotes: ->expression('field_temp', 'UNIX_TIMESTAMP(field)')
    – rooby
    Commented Sep 16, 2013 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.