1

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.

rotsner
  • 642
  • 3
  • 6
  • 23

1 Answers1

0

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

rotsner
  • 642
  • 3
  • 6
  • 23