Inserting the value from Datetime field form

Question

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).

Answer 1

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.

Answer 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');

Answer 3

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

Answer 4

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);