The Java implementation uses ChronoUnit.between to calculate years, months, days, and hours transforms for timestamp and date types. For dates and timestamps before 1970, the values are off by 1 because units are rounded down.
For example, 1969-12-31 23:59:59.000000 is -1,000,000 in microseconds. When that value is converted to hours by dividing by 3,600,000,000 microseconds per hour, the value is 0 instead of -1. But 1 second after new year 1970 results in the same value, 0. This affects all timestamp transforms and the year/month date transforms (days with date values is correct because the interval is a whole number of days).
This was discussed in the community and the consensus was to document the current behavior of the year, month, day, and hour transforms in the spec so that any existing data can be read correctly. Then, new transforms without this issue should be introduced and used by default for v2 tables.
The Java implementation uses
ChronoUnit.betweento calculate years, months, days, and hours transforms for timestamp and date types. For dates and timestamps before 1970, the values are off by 1 because units are rounded down.For example, 1969-12-31 23:59:59.000000 is -1,000,000 in microseconds. When that value is converted to hours by dividing by 3,600,000,000 microseconds per hour, the value is 0 instead of -1. But 1 second after new year 1970 results in the same value, 0. This affects all timestamp transforms and the year/month date transforms (days with date values is correct because the interval is a whole number of days).
This was discussed in the community and the consensus was to document the current behavior of the
year,month,day, andhourtransforms in the spec so that any existing data can be read correctly. Then, new transforms without this issue should be introduced and used by default for v2 tables.