Apache embedded derby TIMESTAMPDIFF function returning 1 hour additional after day light saving

Question

My application is using apache embedded derby. It is reading one of the timestamp column say arrival_time and calculating the interval from the database current time.

select {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,timestamp('1970-01-01 00:00:00'),CURRENT_TIMESTAMP)} - min(arrival_time) / 1000 as "TIME" from APP.myTable

This query was working properly but after day light saving it is not returning me proper interval. Time diff is always 1 hour additional.

To debug further I executed below queries -

select CURRENT_TIMESTAMP from APP.myTable;
select {fn TIMESTAMPDIFF(SQL_TSI_SECOND,timestamp('1970-01-01 00:00:00'),CURRENT_TIMESTAMP)} from APP.myTable;

Output for the above query was as follows -

2017-01-09 07:45:55 //current timestamp

1483951554 // interval

As per my understanding interval should be equivalent to current timestamp but when I am trying to convert the interval into epoch timestamp following is the result-

GMT: Mon, 09 Jan 2017 08:45:54 GMT

Timezone in use is GMT only. Please let me know where I am going wrong.

Thanks in Advance.

Answer 1

As I could not figure out the root cause for the above issue I changed my logic. Created user defined function in derby to calculate the interval. Wrote my function in java language to calculate the difference and it worked.

class DerbyFunction{ public static long getInterval(long timestamp){ return System.currentTimeMillis() - timestamp; } }

select APP.getInterval(arrival_time) as "TIME" from APP.myTable

To create user defined functions in apache derby refer to following location - Creating Apache Derby User defined Functions