By default, we do have only 3 custom date fields (date1, date2, date3) and if we are going to use addition custom date fields (say date4,date5,date6 etc) then we have to go one level up by using extended custom attributes id.
Level 1: date1 date2 date3
Level 2: date4 date5 date6
Level 3: date7 date8 date9
Let's say an Entity has a custom date1, custom date5, and custom date9. The custom date1 value will be stored at custom date1 of the customattribute table. If we have additional custom attribute then the customattribute table will have extended_customattriute_id field with some id and then picking up this id we have again query in customattribute table to get the date4,date5,date6 value and so on.
Custom date4 will be stored at the date1 field to the next level and this goes on.
Please see the sample attached query that will pull up the extended custom attributes fields.
This will be applicable to all type of custom attributes.
select a.name,
a.asset_type,
cu.date1,
cu.date2,
cu.date3,
ecu1.date1 date4,
ecu1.date2 date5,
ecu1.date3 date6,
ecu2.date1 date7,
ecu2.date2 date8,
ecu2.date3 date9
from agl_asset a
left join agl_customattributes cu on a.asset_customattributes_id=cu.customattributes_id
left join agl_customattributes ecu1 on ecu1.customattributes_id=cu.extendedcustomattributes_id
left join agl_customattributes ecu2 on ecu2.customattributes_id=ecu1.extendedcustomattributes_id
where a.name='aaa';