Introduction
The date module enables site users to create so-called datestamp fields,
which use a unix timestamp for storing date values of any entity.
Problem
Currently, the date module doesn't catch the exception when a user enters a date
inside an entity form which can't be represented by the defined integer stamp.
This happens when the date is below the year 1902 or above 2037.

In this case, the user
get an error - or more precisely an exception - message which may look like this:

PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'field_date_stamp_value2' at row 1: INSERT INTO {field_data_field_date_stamp} (entity_type, entity_id, revision_id, bundle, delta, language, field_date_stamp_value, field_date_stamp_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] => 2 [:db_insert_placeholder_2] => 2 [:db_insert_placeholder_3] => article [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 1893452460 [:db_insert_placeholder_7] => 2150846145 ) in field_sql_storage_field_storage_write() (line 494 of .../modules/field/modules/field_sql_storage/field_sql_storage.module).

Proposed solution
Catch this exception and show a form error message like "Error on Date #1: The entered date can't be represented by a timestamp. The date must lay between the year 1902 and 2037.".
I think on the Date Field Types documentation should be mentioned that a datestamp module can't handle all dates, only in the mentioned range (although a technical user should be aware of the limits of a unix timestamp).

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Steven Jones’s picture

An alternative solution would be to change the column size to big, so that in MySQL the field is a BIGINT not an INT.

Going to work on one of these.

Steven Jones’s picture

Status: Active » Needs review
FileSize
2.53 KB

Here's a patch that changes the field schema definition and has an update hook that should process all field_sql_storage backed fields so that they become bigint columns, rather than just int columns. This should allow a much greater (although still technically finite) range of dates to be stored.
This should suffice if you can't change your field types over to one of the other formats that don't have the 32bit limitation.

Steven Jones’s picture

Version: 7.x-2.x-dev » 7.x-2.10
Status: Needs review » Needs work

Sorry, that patch needs a semi-trivial re-roll for 7.x-2.x, but applies cleanly to 7.x-2.10, the latest stable version at time of writing.

Steven Jones’s picture

Status: Needs work » Closed (duplicate)
Related issues: +#2016787: Far dates causes PDOException 22003

Ah. Should have checked the dev version.

This is fixed in #2016787: Far dates causes PDOException 22003