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: TEIID30070
ErrorCode: 0
And here's where we can use our workaround:
select
timestampadd
(
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:
select
timestampadd
(
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
