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