Use Values Larger Than 2147483647 with FROM_UNIXTIME

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