Convert unix epoch time to a date in Apache Derby

Question

Is there a function in Apache Derby than can convert a unix epoch time value (eg. 1453974057 ) into a date?

Answer 1

If you have the seconds since unix epoch, use:

select
    {fn TIMESTAMPADD(SQL_TSI_SECOND, 1453974057, TIMESTAMP('1970-01-01-00.00.00.000000')) } as DT
from sysibm.SYSDUMMY1

Just replace "sysibm.SYSDUMMY1" with your original table, and replace 1453974057 with your value.

When dealing with milliseconds it gets a bit more complicated because you can't just use TIMESTAMPADD directly (you get SQL state 22003: The resulting value is outside the range for the data type INTEGER.)

If you have the milliseconds since unix epoch, use:

select
    --the following block converts milliseconds since linux epoch to a timestamp
    { fn TIMESTAMPADD(
        SQL_TSI_FRAC_SECOND,
        (
            --add the millisecond component
            1453974057235 - { fn TIMESTAMPDIFF(
                SQL_TSI_SECOND,
                TIMESTAMP('1970-01-01-00.00.00.000000'),
                { fn TIMESTAMPADD(SQL_TSI_SECOND, 1453974057235/1000, TIMESTAMP('1970-01-01-00.00.00.000000')) }
            )} * 1000
         ) * 1000000,
         { fn TIMESTAMPADD(SQL_TSI_SECOND, 1453974057235/1000, TIMESTAMP('1970-01-01-00.00.00.000000')) }
    )} as FINAL_DT
from SYSIBM.SYSDUMMY1

Just replace the 3 instances of 1453974057235 with your value.