2

I have a form where the user can input datetime and then I want to store it in the database. Here's my schema :

'start' => array( 'type' => 'varchar', 'mysql_type' => 'datetime', 'not null' => FALSE, ),

The form :

$form['add']['start'] = array( '#type' => 'datetime', '#default_value' => $date, );

When I hit submit button, it shows me :

db_insert failed. Message = SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value:...

What I got out of it was that the DateTime in Drupal is incompatible with MySQL's. Any suggestions or how should I process the value from the form so I can insert it into the db?

Alternatively, any way to convert DrupalDatetime into string?

(For what it's worth I also tried 'timestamp' as the MySQL type but it throws me the same error).

4 Answers 4

16

Datetime fields in Drupal 8 are stored as strings, partially because date support between databases isn't consistent and partially to make them easy to manipulate to extract parts where there aren't proper date functions.

The date is stored with the format DATETIME_DATETIME_STORAGE_FORMAT, and the timezone is implicitly DATETIME_STORAGE_TIMEZONE.

You would need to do something like:

Before Drupal 8.5

/** @var \Drupal\Core\Datetime\DrupalDateTime $date */
$date->setTimezone(new \DateTimezone(DATETIME_STORAGE_TIMEZONE));
$string = $date->format(DATETIME_DATETIME_STORAGE_FORMAT);

In Drupal 8.5 and after, as per https://www.drupal.org/node/2912980

/** @var \Drupal\Core\Datetime\DrupalDateTime $date */
$date->setTimezone(new \DateTimezone(DateTimeItemInterface::STORAGE_TIMEZONE));
$string = $date->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT);

There are better ways to handle this, though. It is best to use the date formatter service to format the dates, rather than the ::format() method (I'm being lazy here). It is also probably best to use proper entities and add this as a field, and not just stuff it into a database table.

4
  • So they are strings? Darn, would've saved me lots of hours if I knew that. Though admittedly I am new at Drupal and the part you wrote about entities and fields lost me, but I got my answer :) thank you.
    – bravely23
    Jun 14, 2016 at 15:47
  • @bravely23 In Drupal 8, database content should be saved via entities. See drupal.org/developing/api/entity for more information about entities.
    – Eyal
    Jun 14, 2016 at 22:21
  • For date only (no hour / minute) use DATETIME_DATE_STORAGE_FORMAT Jun 14, 2017 at 18:04
  • Usage of DATETIME_DATETIME_STORAGE_FORMAT is now deprecated, use instead \Drupal\datetime\Plugin\Field\FieldType\DateTimeItemInterface::DATETIME_STORAGE_FORMAT
    – mperrin
    Jun 25, 2018 at 7:48
2

any way to convert DrupalDatetime into string?

Yes, use the format method, e.g.

$formatted_date = $date->format('Y-m-d H:i:s');
0
1

I am not really sure what you are trying to do and on what API level you work. But if you try to store a timestamp in a sql datetime field you should use date('c') to handover the ISO 8601 date as string to mysql.

Regards,

Rainer

0

The complete answer above did not work for me, be gave me the clue to look for the storage formats.

My date was a DATE format (and note DATETIME as in the given example), so just use the right constant.

constant DATETIME_STORAGE_FORMAT :: string(12) "Y-m-d\TH:i:s"
constant DATE_STORAGE_FORMAT :: string(5) "Y-m-d"

Working example.

$dtime = DateTime::createFromFormat("m/d/Y - G:i", $row->Date); // not needed
$dtime->setTimezone(new \DateTimezone(DateTimeItemInterface::STORAGE_TIMEZONE));
$dtimeFormat = $dtime->format(DateTimeItemInterface::DATE_STORAGE_FORMAT);

$article->set('field_date', $dtimeFormat);

Your Answer

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

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