Newer releases have FROM_MILLIS - [TEIID-5026] Add function to transform timestamp into Unix time - JBoss Issue Tracker
If you don't care about the millisecond resolution, you can just divide by 1000 and cast to an integer.
Already tried that:
TEIID30328 Unable to evaluate convert(253402214400, integer): TEIID30384 Error while evaluating function convert
Which is in seconds instedad of miliseconds.
Which is logical why it fails because the value is bigger than allowed on a java integer:
signed 32 bit -2 147 483 648 +2 147 483 647
Is that your upper bound value? That would be December 30th, 9999.
In general the limit of signed integer seconds is January 2038 - the end of unix time. If you only need special handling for the max, you can use a case statement, otherwise you'll need to use a UDF if you are on an older version.
That is correct, some fields have dates that are in the year 9999.
I fixed it by creating a virtual procedure which can convert it for me. Perhaps not the most elegant way but it does work and is robust. It also takes into account timezones and seconds/milliseconds.