Repository: duckdb/duckdb (language: python)

Issue:
"Information loss on integer cast" for pre-1970 timestamp_ns
### What happens?

The query "select '1969-01-01T23:59:59.9999999'::timestamp_ns;" gives the following error:

```
INTERNAL Error: Information loss on integer cast: value -99952 outside of target range [-128, 127]
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors
```

### To Reproduce

Run the query
```sql
select '1969-01-01T23:59:59.9999999'::timestamp_ns;
```
on DuckDB v1.1.3 19864453f7. The error will appear.

### OS:

MacOS ARM (Darwin 23.6.0 Darwin Kernel Version 23.6.0: Mon Jul 29 21:14:04 PDT 2024; root:xnu-10063.141.2~1/RELEASE_ARM64_T8122 arm64)

### DuckDB Version:

v1.1.3 19864453f7

### DuckDB Client:

Command-line "duckdb" on MacOS

### Hardware:

MacBook Air Apple M3

### Full Name:

Eirik Bakke

### Affiliation:

Ultorg Inc.

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
Hi @eirikbakke, thanks for reporting this. I could also reproduce the error on the nightly build.

Fix patch:
diff --git a/src/common/types/timestamp.cpp b/src/common/types/timestamp.cpp
index dbb1202d002e..f23ee2146c40 100644
--- a/src/common/types/timestamp.cpp
+++ b/src/common/types/timestamp.cpp
@@ -17,6 +17,15 @@ namespace duckdb {
 
 static_assert(sizeof(timestamp_t) == sizeof(int64_t), "timestamp_t was padded");
 
+// Temporal values need to round down when changing precision,
+// but C/C++ rounds towrds 0 when you simply divide.
+// This piece of bit banging solves that problem.
+template <typename T>
+static inline T TemporalRound(T value, T scale) {
+	const auto negative = int(value < 0);
+	return UnsafeNumericCast<T>((value + negative) / scale - negative);
+}
+
 // timestamp/datetime uses 64 bits, high 32 bits for date and low 32 bits for time
 // string format is YYYY-MM-DDThh:mm:ssZ
 // T may be a space
@@ -342,7 +351,7 @@ void Timestamp::Convert(timestamp_t timestamp, date_t &out_date, dtime_t &out_ti
 }
 
 void Timestamp::Convert(timestamp_ns_t input, date_t &out_date, dtime_t &out_time, int32_t &out_nanos) {
-	timestamp_t ms(input.value / Interval::NANOS_PER_MICRO);
+	timestamp_t ms(TemporalRound(input.value, Interval::NANOS_PER_MICRO));
 	out_date = Timestamp::GetDate(ms);
 	int64_t days_nanos;
 	if (!TryMultiplyOperator::Operation<int64_t, int64_t, int64_t>(out_date.days, Interval::NANOS_PER_DAY,


Test patch:
diff --git a/test/sql/types/timestamp/test_timestamp_types.test b/test/sql/types/timestamp/test_timestamp_types.test
index 436576c5346b..6ac928586134 100644
--- a/test/sql/types/timestamp/test_timestamp_types.test
+++ b/test/sql/types/timestamp/test_timestamp_types.test
@@ -74,6 +74,12 @@ AS tbl(s)
 2024-06-04 10:17:10.9
 2024-06-04 10:17:10
 
+# Negative timestamp_ns
+query I
+select '1969-01-01T23:59:59.9999999'::timestamp_ns;
+----
+1969-01-01 23:59:59.9999999
+
 # TIME conversions are now supported
 query I
 select sec::TIME from timestamp;