Splice Machine: TIMESTAMPADD returns value that is 1 hour off 10-15% of the time

Question

Running into a weird bug when executing TIMESTAMPADD queries where the result is not always accurate.

Example #1 (incorrect):

TIMESTAMPADD(SQL_TSI_SECOND, 1214870399, TIMESTAMP('1970-01-01 00:00:00.000Z'))

Returns: 2008-07-01 00:59:59.0

It should be: 2008-06-30 23:59:59.0

Example #2 (correct):

TIMESTAMPADD(SQL_TSI_SECOND, 1167609600, TIMESTAMP('1970-01-01 00:00:00.000Z'))

Returns: 2007-01-01 00:00:00.0 which is correct.

It happens with roughly 10-15% of my queries (lots of unixtime to timestamp converting when querying my tables). It is always the same 1 hour off.

Thanks

Edit with additional information:

Other example unixtimes that show up incorrectly if I try to convert:

• 1270508410 to 2010-04-06 00:00:10.0 which should be 2010-04-05 23:00:10.0
• 1304722810 to 2011-05-07 00:00:10.0 which should be 2011-05-06 23:00:10.0
• 1340221507 to 2012-06-20 20:45:07.0 which should be 2012-06-20 19:45:07.0

This last one is just to show its not just related to timestamps that are near the midnight time period.

Answer 1

Turns out Splice Machine has their own open issue about this problem.

For reference when reaching out to Splice Machine support: Ticket number DB-4937