At present, Data Virtuality Server only supports integers in FROM_UNIXTIME and TIMESTAMPADD. As 2147483647 is the maximum positive value for a 32-bit signed binary integer, this means that larger values won’t work. But we have a workaround for that.
Let’s consider an example where the value is greater than 2147483647:
select from_unixtime( 4154192899 )
This query return the following:
Error: TEIID30070 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'from_unixtime(4154192899)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.SQLState: TEIID30070ErrorCode: 0
And here’s where we can use our workaround:
selecttimestampadd( SQL_TSI_SECOND, cast( mod( 4154192899,60) as integer) , timestampadd ( SQL_TSI_MINUTE, cast( 4154192899/60 as integer), MODIFYTIMEZONE ({ts '1970-01-01 00:00:00'}, 'GMT' ) ))
This time, it will work, and our query will return the following result:
2101-08-23 00:28:19.0
To check that the workaround does indeed work, you can test it with any value lesser than 2147483647. Here’s our example:
select from_unixtime( 2124192889 )
It will return the following:
2037-04-24 15:34:49
Let’s use our workaround now and check if the response will be the same:
selecttimestampadd( SQL_TSI_SECOND, cast( mod( 2124192889,60) as integer) , timestampadd ( SQL_TSI_MINUTE, cast( 2124192889/60 as integer), MODIFYTIMEZONE ({ts '1970-01-01 00:00:00'}, 'GMT' ) ))
And indeed, the result will be the same as above:
2037-04-24 15:34:49.0