PDOException when saving date as unix timestamp

Same problem. I have the same field in a local environment and now threw that into production site via feature and get this error in the production site - but not on a local site.

Also added a new field (date, unix stamp) manually through GUI and get the same error for that field also.

PDOException: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'field_active_timestamp_value' at row 1: INSERT INTO {field_data_field_active_timestamp} (entity_type, entity_id, revision_id, bundle, delta, language, field_active_timestamp_value, field_active_timestamp_value2) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 47250 [:db_insert_placeholder_2] => 53796 [:db_insert_placeholder_3] => broadcast [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 2013-02-21 12:32 [:db_insert_placeholder_7] => 2013-02-21 13:32 ) funktiossa field_sql_storage_field_storage_write() (rivi 448 tiedostossa /modules/field/modules/field_sql_storage/field_sql_storage.module).

EDIT 2013-03-06: problem went away. No idea how and why :((((

I just encoutered the exact same probmem using Date field with unix stamp.

The data cannot be injected in the DB...

I am not sure, but when you see this in the error message :
[:db_insert_placeholder_6] => 2013-02-21 12:32 [:db_insert_placeholder_7] => 2013-02-21 13:32

...you guess that drupal tries to inject a date that is not in unix-timestamp format in a column that is supposed to receive only unixtimestamp format INT(11).

Also, what is really strange, is that this field type (unix timestamp) used to work a few days ago. This morning I wake up, and the field doesn't work anymore.

Do someone has a clue ?

Same error here. Do you solved it?

EDIT: As my debugs, (like @tormu and @titi88888888 said) the value is not being sent as timestamp (it's being sent as the format defined in the instance field). That is what is causing the problem. But in my case, the error only happens on a particular bundle (strange, not?). I tested this field (exactly the same field) in other 2 bundles and functioned normally. I will continue to investigate the problem...

Hey guys. My workaround was to get the field value (which is formated as d/m/Y) in form #validate and force the timestamp value with strtotime(). I hope to find a permanent solution soon. If anyone finds, please let me (us) know ;)

Hey guys, I was getting this issue too and after a whole day debugging I finally found the cause, still don't know how to solve it yet though. In my case the problem is caused because I'm using conditional fields module together with date module. Problem arises because date element validation handler changes the value from datetime format to datestamp format, but in around line 521 of date_elements.inc file there is the following

  // Don't look for further errors if errors are already flagged
  // because otherwise we'll show errors on the nested elements
  // more than once.
  elseif (!form_get_errors()) {

it triggers the action depending if ANY error was triggered by any other field before, now, around line 789 of conditional_fields.module there is the following

    // Since Drupal provides no clean way to selectively remove error messages,
    // we have to store all current form errors and error messages, clear them,
    // filter out from our stored values the errors originating from untriggered
    // dependent fields, and then reinstate remaining errors and messages.
    $errors = array_diff_assoc((array) form_get_errors(), $untriggered_dependents_errors);
    form_clear_error();

so "invalid" errors are cleared and the form submits but date field assumed there was an error so it never "processed" the date field value so it's passed through as is, which is in datetime format. Still not sure how to fix this but if you have any idea let me (us) know. Btw, I tried to set a negative weight to the conditional fields module but it didn't work.

I can confirm that this indeed does have to do with conditional fields.
Even if the date field is not in any of the conditions.

Did you ever figure out how to resolve this?

@Ericmaster, actually I think you may have had that backwards. I set the conditional fields module to a lower weight (30, it was set to 10 originally) and I am now able to save unix timesamp date fields with conditional fields.

Thanks for that clue.

Good to hear that modules weight worked for you, sorry for not being able to respond quickly. At the end I workaround it by setting all fields as not required and redo validation in a custom validation handler, not the best solution of course, but needed to close that bug asap.

Any update on this issue? I have required fields, which are made hidden by conditional field module in certain conditions, of course that will bring validation errors when saving form, those errors are removed by conditional field module, but the date field is not converted into UNIX format. Is there a way to process all visible fields, before removing error?
Setting module priority doesn't help. I had to remove "Required field" on all fields to make this error go away :/

#4 worked for me. In my case, the workaround code in a custom module looks like this...

function mymodule_form_user_profile_form_alter(&$form, &$form_state) {
  $form['#validate'][] = 'mymodule_user_profile_form_fixup_expiration';
}

function mymodule_user_profile_form_fixup_expiration($form, &$form_state) {
  if (!is_numeric($form_state['values']['field_commerce_sp_validity'][LANGUAGE_NONE][0]['value'])) {
    $form_state['values']['field_commerce_sp_validity'][LANGUAGE_NONE][0]['value'] = strtotime($form_state['values']['field_commerce_sp_validity'][LANGUAGE_NONE][0]['value']);
  }
}

I am having the same issue updating a user via Services. With the workarounds discussed you will likely face issues with timezones.

The attached patch fixes the issue for me. The issue being that $form_input[$field_name] is empty but $form_values[$field_name] is not. Please confirm if this solves the problem with conditional fields.

An alternative would be to ensure that date_hidden_element() returns TRUE. To achieve that one of the following conditions must be met.

1. $element['#access'] is false/0
2. $element['#programmed'] is true
3. $element['#type'] is hidden or value

In my scenario that probably doesn't make sense, as Services has no idea that a user profile might have a date field (or that the date module even exists).

The patch could also be rewritten to share the same return statement at the top of the function, but that would require moving the "expensive" drupal_array_get_nested_value() calls to the top.

One more approach that could solve this is to ensure $form_input[$field_name] is always set before this function is called, but I have no idea where to start to achieve that.

I got the same problem but only after upgrading conditional fields to latest dev for testing. With version 7.x-3.0-alpha1 of conditional fields the problem is NOT present

Short update:

I have recreated my conditons and i can't reproduce the error now. Can anyone confirm this?

I am getting the error with both alpha-1 and 3.x-dev releases of conditional_fields.

It may have solved the problem that i was recreating the conditions while migrating between the versions

Thanks @Ericmaster for his work tracking this down! Changing the required fields to not required and creating a custom validation handler is a good workaround.

I am not sure how changing module order would fix this, as I believe that conditional_fields_form_validate needs to run after node_form_validate and it is node_form_validate that executes the field element validation. The problem is that if you have a required field that is hidden by conditional_fields AND is validated prior to a date field then the date module in date_combo_validate will not convert the date strings to timestamps and a PDO Exception will be thrown.

One possible (bad) solution is to just change the elseif to an else in the following code and let the duplicate errors fly:

  // Don't look for further errors if errors are already flagged
  // because otherwise we'll show errors on the nested elements
  // more than once.
  elseif (!form_get_errors()) {

However, I am thinking there should be better way to avoid showing duplicate errors on nested elements and not throw exceptions when using conditional_fields.

Problem continues on newest version 7.x-2.8

Change priority because I think it's a big problem if you decided used this date format.

The patch from #11 fixed an issue I was having with updating a Node that included this type via the Services module.

I had the same issue as #20. Was using Services module. User profile had a "Date of Birth" field of type date, storing as unix timestamp. The patch in #11 fixed the issue.

We had the same issue as described in #5 and #18, which appears to be different from the issue with Services module and the patch from #11 doesnt fix it.

New patch with suggested fix is attached

I can confirm that the patch from #11 fixes the Services module issue. I was not using the conditional fields module.

better patch format

#27 works perfect! Thanks, tatyana. I don't have the service module enabled, but conditional fields were used.

Moving back to needs review, state was not changed with last patch submission.

Changes in #11 worked, but patch didn't apply to 7.x-2.9. I pulled a new patch.

Patch #27 works fine , patch #32 not working for me

Patch #32 did not solve the problem with 7.x-2.9 and conditional fields is enabled

Yes, #27 worked; #32 did not.

Moving into RTBC. Change seems good and multiple users have confirmed that the patch in #27 works.

I've applied the patch from #27 to 7x-2.x, which applied OK with a few lines of offset. I did make a small cosmetic code change:

--- a/date_elements.inc
+++ b/date_elements.inc
@@ -571,7 +571,8 @@ function date_combo_validate($element, &$form_state) {
       form_set_value($element[$to_field], $to_date, $form_state);
       $errors[] = t('The End date must be greater than the Start date.');
     }
-    else{
-
+    else {
       // Convert input dates back to their UTC values and re-format to ISO
       // or UNIX instead of the DATETIME format used in element processing.
       $item[$tz_field] = $timezone;

Thanks for your work on this!

#32 is necessary for compatibility with the Services module. Can we take a look at merging in #32 as well?

#32 merged. Thanks.

The problem is still relevant.

PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'field_date_value' at row 1: INSERT INTO {field_data_field_date} (entity_type, entity_id, revision_id, bundle, delta, language, field_date_value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 70 [:db_insert_placeholder_2] => 70 [:db_insert_placeholder_3] => event [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => -62165769799 ) в функции field_sql_storage_field_storage_write() (строка 514 в файле /home/srv66997/lukmkp.ru/modules/field/modules/field_sql_storage/field_sql_storage.module).

see the data field value is negative. How can this be?

@evadex It was released just 2 weeks ago: https://www.drupal.org/project/date/releases/7.x-2.10