Repository: duckdb/duckdb (language: python)

Issue:
Improving CI performance
This pr contains a few changes to the CI that should bring a decent reduction in overal CI runner load.

1.  For Node and Python, we only run the latest and oldest versions if the branch != master
2. When a PR only touches files for node/python/R or their respective yml files, we only run the CI thats actually relevant.
3.  OSX CI is never run for PRs, only after merging on the push event of master. Reasoning behind this is that if a test passes linux CI it will almost certainly also pass OSX ci and the OSX runners tend to be a bottleneck.

**Potential further improvements**
I could move tools/odbc and tools/jdbc into separate yml files and add similar optimization to only run the relevant ci for those directories. I could also add this to this PR?

For more drastic changes in CI runner load we could consider running the CI only on a specific subset pull_request events. For example we could only run the CI on the pull_request.opened and and pull_request.review_requested events (see [github docs](https://docs.github.com/en/actions/using-workflows/events-that-trigger-workflows#pull_request)), which would mean that CI will run automatically on the initial pr open, but for the ci to be rerun when the author has made changes, the author has to explicitly request a review, which will then trigger the CI. However, whether this is a development workflow we want is up for discussion. I'd say it makes sense to wait out this pr and see if CI queue/run times improve enough to leave it as is.


Discussion / hints:
Thanks! Looks excellent. It seems the NodeJS and Python OSX builds are still running in the PR, can we disable those as well for PRs?

Fix patch:
diff --git a/src/core_functions/scalar/date/date_diff.cpp b/src/core_functions/scalar/date/date_diff.cpp
index 6266dda3503c..36376a2b5711 100644
--- a/src/core_functions/scalar/date/date_diff.cpp
+++ b/src/core_functions/scalar/date/date_diff.cpp
@@ -28,6 +28,14 @@ struct DateDiff {
 		    });
 	}
 
+	//	We need to truncate down, not towards 0
+	static inline int64_t Truncate(int64_t value, int64_t units) {
+		return (value + (value < 0)) / units - (value < 0);
+	}
+	static inline int64_t Diff(int64_t start, int64_t end, int64_t units) {
+		return Truncate(end, units) - Truncate(start, units);
+	}
+
 	struct YearOperator {
 		template <class TA, class TB, class TR>
 		static inline TR Operation(TA startdate, TB enddate) {
@@ -204,30 +212,28 @@ template <>
 int64_t DateDiff::MillisecondsOperator::Operation(timestamp_t startdate, timestamp_t enddate) {
 	D_ASSERT(Timestamp::IsFinite(startdate));
 	D_ASSERT(Timestamp::IsFinite(enddate));
-	return Timestamp::GetEpochMs(enddate) - Timestamp::GetEpochMs(startdate);
+	return Diff(startdate.value, enddate.value, Interval::MICROS_PER_MSEC);
 }
 
 template <>
 int64_t DateDiff::SecondsOperator::Operation(timestamp_t startdate, timestamp_t enddate) {
 	D_ASSERT(Timestamp::IsFinite(startdate));
 	D_ASSERT(Timestamp::IsFinite(enddate));
-	return Timestamp::GetEpochSeconds(enddate) - Timestamp::GetEpochSeconds(startdate);
+	return Diff(startdate.value, enddate.value, Interval::MICROS_PER_SEC);
 }
 
 template <>
 int64_t DateDiff::MinutesOperator::Operation(timestamp_t startdate, timestamp_t enddate) {
 	D_ASSERT(Timestamp::IsFinite(startdate));
 	D_ASSERT(Timestamp::IsFinite(enddate));
-	return Timestamp::GetEpochSeconds(enddate) / Interval::SECS_PER_MINUTE -
-	       Timestamp::GetEpochSeconds(startdate) / Interval::SECS_PER_MINUTE;
+	return Diff(startdate.value, enddate.value, Interval::MICROS_PER_MINUTE);
 }
 
 template <>
 int64_t DateDiff::HoursOperator::Operation(timestamp_t startdate, timestamp_t enddate) {
 	D_ASSERT(Timestamp::IsFinite(startdate));
 	D_ASSERT(Timestamp::IsFinite(enddate));
-	return Timestamp::GetEpochSeconds(enddate) / Interval::SECS_PER_HOUR -
-	       Timestamp::GetEpochSeconds(startdate) / Interval::SECS_PER_HOUR;
+	return Diff(startdate.value, enddate.value, Interval::MICROS_PER_HOUR);
 }
 
 // TIME specialisations


Test patch:
diff --git a/test/sql/function/timestamp/test_date_diff_epoch.test b/test/sql/function/timestamp/test_date_diff_epoch.test
new file mode 100644
index 000000000000..4402462e3be2
--- /dev/null
+++ b/test/sql/function/timestamp/test_date_diff_epoch.test
@@ -0,0 +1,66 @@
+# name: test/sql/function/timestamp/test_date_diff_epoch.test
+# description: Test equal period timestamp diffs across the Unix epoch
+# group: [timestamp]
+
+statement ok
+PRAGMA enable_verification
+
+query III
+SELECT 
+	start_ts, 
+	end_ts, 
+	DATEDIFF('day', start_ts, end_ts) AS dd_hour 
+FROM VALUES (
+	'1970-01-03 12:12:12'::TIMESTAMP, 
+	'1969-12-25 05:05:05'::TIMESTAMP
+	) x(start_ts, end_ts);
+----
+1970-01-03 12:12:12	1969-12-25 05:05:05	-9
+
+query III
+SELECT 
+	start_ts, 
+	end_ts, 
+	DATEDIFF('hour', start_ts, end_ts) AS dd_hour 
+FROM VALUES (
+	'1970-01-01 12:12:12'::TIMESTAMP, 
+	'1969-12-31 05:05:05'::TIMESTAMP
+	) x(start_ts, end_ts);
+----
+1970-01-01 12:12:12	1969-12-31 05:05:05	-31
+
+query III
+SELECT 
+	start_ts, 
+	end_ts, 
+	DATEDIFF('minute', start_ts, end_ts) AS dd_minute 
+FROM VALUES (
+	'1970-01-01 00:12:12'::TIMESTAMP, 
+	'1969-12-31 23:05:05'::TIMESTAMP
+	) x(start_ts, end_ts);
+----
+1970-01-01 00:12:12	1969-12-31 23:05:05	-67
+
+query III
+SELECT 
+	start_ts, 
+	end_ts, 
+	DATEDIFF('second', start_ts, end_ts) AS dd_second 
+FROM VALUES (
+	'1970-01-01 00:00:12.456'::TIMESTAMP, 
+	'1969-12-31 23:59:05.123'::TIMESTAMP
+	) x(start_ts, end_ts);
+----
+1970-01-01 00:00:12.456	1969-12-31 23:59:05.123	-67
+
+query III
+SELECT 
+	start_ts, 
+	end_ts, 
+	DATEDIFF('millisecond', start_ts, end_ts) AS dd_second 
+FROM VALUES (
+	'1970-01-01 00:00:12.456789'::TIMESTAMP, 
+	'1969-12-31 23:59:05.123456'::TIMESTAMP
+	) x(start_ts, end_ts);
+----
+1970-01-01 00:00:12.456789	1969-12-31 23:59:05.123456	-67333