CODEWITHSUNDEEP
timestampderbyunix-timestampepoch
Fidel
Fidel

Reputation: 7397

Convert unix epoch time to a date in Apache Derby

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

Upvotes: 4

Views: 1219

Answers (2)

user4951497
user4951497

Reputation:

Here is a simplified expression based on Fidel's answer:

select { fn TIMESTAMPADD(SQL_TSI_FRAC_SECOND, mod(1453974057, 1000) * 1000000,
         { fn TIMESTAMPADD(SQL_TSI_SECOND, 1453974057 / 1000,
           TIMESTAMP('1970-01-01-00.00.00.000000'))
         })
       }
from sysibm.SYSDUMMY1;

Tested against a local JavaDB instance.

Upvotes: 1

Fidel
Fidel

Reputation: 7397

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.

Upvotes: 5

Related Questions