basic difference between Date, Date(ISO format), Date(Unix timestamp) to create a field

Question

I have created a field as field type Date(Unix timestamp).

When i edit a content with some input value in the above created field it shows an error like below:-

PDOException: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'field_reprisal_date_value' at row 1: INSERT INTO {field_data_field_reprisal_date} (entity_type, entity_id, revision_id, bundle, delta, language, field_reprisal_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] => 258985 [:db_insert_placeholder_2] => 245170 [:db_insert_placeholder_3] => embedded_video [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 2015-03-02 ) in field_sql_storage_field_storage_write() (line 494 of /www/d7yourtango/modules/field/modules/field_sql_storage/field_sql_storage.module).

But after deleting above field again i create another field as field type Date and i process same, it work.

So my question is why it happening so for 1st case?

And what are basic difference between field types of Date, Date(ISO format), Date(Unix timestamp) to create a field.

Answer 1

Below explains three field types in details

Date

• Called a 'datetime' field type in techno database speak. This format type is recomended for most installs because it takes advantage of the database's date handling functions without any conversion, which means faster queries.
• Stores the date in the database's native date format (YYYY-MM-DD HH:MM:SS).
• The data is in a human-readable form.
• You can use it for incomplete dates, i.e. only a year or only a year and a month, and pad the other values with zeros, so it does not appear to be any more precise than it really is.

Date (ISO Format)

• Stores the date in an ISO format (YYYY-MM-DDTHH:MM:SS).

• The data is in a human-readable form.

• List You can use it for incomplete dates, i.e. only a year or only a year and a month, and pad the other values with zeros, so it does not appear to be any more precise than it really is.

• It is a format that is internationally-recognized, and it is used as-is on many web sites and in many applications.

Date (Unix Timestamp)

• Stores the date as an integer.

• Takes up less room in the database because it's smaller.

• Often easier to use for date calculations because you can increase or decrease it just by adding or subtracting seconds.

• It is the format used by php date functions.

• It must be filled with a complete date -- year, month, day, hour, minute, second, so you sometimes have to arbitrarily set some of these values even if they are not applicable.

Taken Reference from Here