fix(db): set session timezone to UTC

Some (well all except sqlite) database platforms support timezone
configuration. The problem is that we expect everything in UTC,
but some servers might have set some different default (e.g. in database
configuration or even just because of `TZ` environment variable).

This causes incorrect values when expecting `NOW()` to return the
current time in UTC.

For PHP we already enforce UTC as timezone, this PR adds a middleware
that enforces UTC also as the database connection / session timezone.

Signed-off-by: Ferdinand Thiessen <opensource@fthiessen.de>

.github/workflows/phpunit-mariadb.yml

@@ -129,6 +129,9 @@ jobs:
 
       - name: PHPUnit
         run: composer run test:db -- --log-junit junit.xml ${{ matrix.coverage && '--coverage-clover ./clover.db.xml' || '' }}
+        env:
+          DB_ROOT_USER: root
+          DB_ROOT_PASS: rootpassword
 
       - name: Upload db code coverage
         if: ${{ !cancelled() && matrix.coverage }}

.github/workflows/phpunit-mysql.yml

@@ -129,6 +129,9 @@ jobs:
 
       - name: PHPUnit
         run: composer run test:db -- --log-junit junit.xml ${{ matrix.coverage && '--coverage-clover ./clover.db.xml' || '' }}
+        env:
+          DB_ROOT_USER: root
+          DB_ROOT_PASS: rootpassword
 
       - name: Upload db code coverage
         if: ${{ !cancelled() && matrix.coverage }}

autotest.sh

@@ -36,6 +36,18 @@ set -e
 _XDEBUG_CONFIG=$XDEBUG_CONFIG
 unset XDEBUG_CONFIG
 
+# Get the IP address of a docker container
+#
+# @param $1 - The container id
+function docker_get_ip {
+	_docker_version=$(docker --version | grep -Po "(?<=Docker version )\d+")
+	if [ "$_docker_version" -ge 29 ]; then
+		docker inspect --format="{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" "$1"
+	else
+		docker inspect --format="{{.NetworkSettings.IPAddress}}" "$1"
+	fi
+}
+
 function print_syntax {
 	echo -e "Syntax: ./autotest.sh [dbconfigname] [testfile]\n" >&2
 	echo -e "\t\"dbconfigname\" can be one of: $DBCONFIGS" >&2
@@ -201,19 +213,15 @@ function execute_tests {
 				-e MYSQL_PASSWORD=owncloud \
 				-e MYSQL_DATABASE="$DATABASENAME" \
 				-d mysql)
-			DATABASEHOST=$(docker inspect --format="{{.NetworkSettings.IPAddress}}" "$DOCKER_CONTAINER_ID")
+			DATABASEHOST=$(docker_get_ip "$DOCKER_CONTAINER_ID")
 
 		else
-			if [ -z "$DRONE" ] ; then # no need to drop the DB when we are on CI
-				if [ "mysql" != "$(mysql --version | grep -o mysql)" ] ; then
-					echo "Your mysql binary is not provided by mysql"
-					echo "To use the docker container set the USEDOCKER environment variable"
-					exit -1
-				fi
-				mysql -u "$DATABASEUSER" -powncloud -e "DROP DATABASE IF EXISTS $DATABASENAME" -h $DATABASEHOST || true
-			else
-				DATABASEHOST=mysql
+			if [ "mysql" != "$(mysql --version | grep -o mysql)" ] ; then
+				echo "Your mysql binary is not provided by mysql"
+				echo "To use the docker container set the USEDOCKER environment variable"
+				exit -1
 			fi
+			mysql -u "$DATABASEUSER" -powncloud -e "DROP DATABASE IF EXISTS $DATABASENAME" -h $DATABASEHOST || true
 		fi
 		echo "Waiting for MySQL initialisation ..."
 		if ! apps/files_external/tests/env/wait-for-connection $DATABASEHOST 3306 300; then
@@ -235,19 +243,15 @@ function execute_tests {
 				--innodb_file_format=barracuda \
 				--innodb_file_per_table=true)
 
-			DATABASEHOST=$(docker inspect --format="{{.NetworkSettings.IPAddress}}" "$DOCKER_CONTAINER_ID")
+			DATABASEHOST=$(docker_get_ip "$DOCKER_CONTAINER_ID")
 
 		else
-			if [ -z "$DRONE" ] ; then # no need to drop the DB when we are on CI
-				if [ "mysql" != "$(mysql --version | grep -o mysql)" ] ; then
-					echo "Your mysql binary is not provided by mysql"
-					echo "To use the docker container set the USEDOCKER environment variable"
-					exit -1
-				fi
-				mysql -u "$DATABASEUSER" -powncloud -e "DROP DATABASE IF EXISTS $DATABASENAME" -h $DATABASEHOST || true
-			else
-				DATABASEHOST=mysqlmb4
+			if [ "mysql" != "$(mysql --version | grep -o mysql)" ] ; then
+				echo "Your mysql binary is not provided by mysql"
+				echo "To use the docker container set the USEDOCKER environment variable"
+				exit -1
 			fi
+			mysql -u "$DATABASEUSER" -powncloud -e "DROP DATABASE IF EXISTS $DATABASENAME" -h $DATABASEHOST || true
 		fi
 
 		echo "Waiting for MySQL(utf8mb4) initialisation ..."
@@ -273,7 +277,7 @@ function execute_tests {
 				-e MYSQL_PASSWORD=owncloud \
 				-e MYSQL_DATABASE="$DATABASENAME" \
 				-d mariadb)
-			DATABASEHOST=$(docker inspect --format="{{.NetworkSettings.IPAddress}}" "$DOCKER_CONTAINER_ID")
+			DATABASEHOST=$(docker_get_ip "$DOCKER_CONTAINER_ID")
 
 			echo "Waiting for MariaDB initialisation ..."
 			if ! apps/files_external/tests/env/wait-for-connection $DATABASEHOST 3306 300; then
@@ -284,16 +288,12 @@ function execute_tests {
 			echo "MariaDB is up."
 
 		else
-			if [ -z "$DRONE" ] ; then # no need to drop the DB when we are on CI
-				if [ "MariaDB" != "$(mysql --version | grep -o MariaDB)" ] ; then
-					echo "Your mysql binary is not provided by MariaDB"
-					echo "To use the docker container set the USEDOCKER environment variable"
-					exit -1
-				fi
-				mysql -u "$DATABASEUSER" -powncloud -e "DROP DATABASE IF EXISTS $DATABASENAME" -h $DATABASEHOST || true
-			else
-				DATABASEHOST=mariadb
+			if [ "MariaDB" != "$(mysql --version | grep -o MariaDB)" ] ; then
+				echo "Your mysql binary is not provided by MariaDB"
+				echo "To use the docker container set the USEDOCKER environment variable"
+				exit -1
 			fi
+			mysql -u "$DATABASEUSER" -powncloud -e "DROP DATABASE IF EXISTS $DATABASENAME" -h $DATABASEHOST || true
 		fi
 
 		echo "Waiting for MariaDB initialisation ..."
@@ -309,7 +309,7 @@ function execute_tests {
 		if [ ! -z "$USEDOCKER" ] ; then
 			echo "Fire up the postgres docker"
 			DOCKER_CONTAINER_ID=$(docker run -e POSTGRES_DB="$DATABASENAME" -e POSTGRES_USER="$DATABASEUSER" -e POSTGRES_PASSWORD=owncloud -d postgres)
-			DATABASEHOST=$(docker inspect --format="{{.NetworkSettings.IPAddress}}" "$DOCKER_CONTAINER_ID")
+			DATABASEHOST=$(docker_get_ip "$DOCKER_CONTAINER_ID")
 
 			echo "Waiting for Postgres initialisation ..."
 
@@ -320,9 +320,6 @@ function execute_tests {
 
 			echo "Postgres is up."
 		else
-			if [ ! -z "$DRONE" ] ; then
-				DATABASEHOST="postgres-$POSTGRES"
-			fi
 			echo "Waiting for Postgres to be available ..."
 			if ! apps/files_external/tests/env/wait-for-connection $DATABASEHOST 5432 60; then
 				echo "[ERROR] Waited 60 seconds for $DATABASEHOST, no response" >&2
@@ -331,15 +328,13 @@ function execute_tests {
 			echo "Give it 10 additional seconds ..."
 			sleep 10
 
-			if [ -z "$DRONE" ] ; then # no need to drop the DB when we are on CI
-				dropdb -U "$DATABASEUSER" "$DATABASENAME" || true
-			fi
+			dropdb -U "$DATABASEUSER" "$DATABASENAME" || true
 		fi
 	fi
 	if [ "$DB" == "oci" ] ; then
 		echo "Fire up the oracle docker"
 		DOCKER_CONTAINER_ID=$(docker run -d deepdiver/docker-oracle-xe-11g)
-		DATABASEHOST=$(docker inspect --format="{{.NetworkSettings.IPAddress}}" "$DOCKER_CONTAINER_ID")
+		DATABASEHOST=$(docker_get_ip "$DOCKER_CONTAINER_ID")
 
 		echo "Waiting for Oracle initialization ... "
 
@@ -397,7 +392,7 @@ function execute_tests {
 	fi
 
 	echo "$PHPUNIT" --fail-on-warning --fail-on-risky --display-warnings --display-deprecations --display-phpunit-deprecations --colors=always --configuration phpunit-autotest.xml $GROUP $COVER --log-junit "autotest-results-$DB.xml" "$2" "$3"
-	"$PHPUNIT" --fail-on-warning --fail-on-risky --display-warnings --display-deprecations --display-phpunit-deprecations --colors=always --configuration phpunit-autotest.xml $GROUP $COVER --log-junit "autotest-results-$DB.xml" "$2" "$3"
+	DB_ROOT_PASSWORD=owncloud DB_ROOT_USER="root" "$PHPUNIT" --fail-on-warning --fail-on-risky --display-warnings --display-deprecations --display-phpunit-deprecations --colors=always --configuration phpunit-autotest.xml $GROUP $COVER --log-junit "autotest-results-$DB.xml" "$2" "$3"
 	RESULT=$?
 
 	if [ "$PRIMARY_STORAGE_CONFIG" == "swift" ] ; then

lib/composer/composer/autoload_classmap.php

@@ -1613,6 +1613,8 @@ return array(
     'OC\\DB\\ConnectionFactory' => $baseDir . '/lib/private/DB/ConnectionFactory.php',
     'OC\\DB\\DbDataCollector' => $baseDir . '/lib/private/DB/DbDataCollector.php',
     'OC\\DB\\Exceptions\\DbalException' => $baseDir . '/lib/private/DB/Exceptions/DbalException.php',
+    'OC\\DB\\Middleware\\UtcTimezoneMiddleware' => $baseDir . '/lib/private/DB/Middleware/UtcTimezoneMiddleware.php',
+    'OC\\DB\\Middleware\\UtcTimezoneMiddlewareDriver' => $baseDir . '/lib/private/DB/Middleware/UtcTimezoneMiddlewareDriver.php',
     'OC\\DB\\MigrationException' => $baseDir . '/lib/private/DB/MigrationException.php',
     'OC\\DB\\MigrationService' => $baseDir . '/lib/private/DB/MigrationService.php',
     'OC\\DB\\Migrator' => $baseDir . '/lib/private/DB/Migrator.php',

lib/composer/composer/autoload_static.php

@@ -1654,6 +1654,8 @@ class ComposerStaticInit749170dad3f5e7f9ca158f5a9f04f6a2
         'OC\\DB\\ConnectionFactory' => __DIR__ . '/../../..' . '/lib/private/DB/ConnectionFactory.php',
         'OC\\DB\\DbDataCollector' => __DIR__ . '/../../..' . '/lib/private/DB/DbDataCollector.php',
         'OC\\DB\\Exceptions\\DbalException' => __DIR__ . '/../../..' . '/lib/private/DB/Exceptions/DbalException.php',
+        'OC\\DB\\Middleware\\UtcTimezoneMiddleware' => __DIR__ . '/../../..' . '/lib/private/DB/Middleware/UtcTimezoneMiddleware.php',
+        'OC\\DB\\Middleware\\UtcTimezoneMiddlewareDriver' => __DIR__ . '/../../..' . '/lib/private/DB/Middleware/UtcTimezoneMiddlewareDriver.php',
         'OC\\DB\\MigrationException' => __DIR__ . '/../../..' . '/lib/private/DB/MigrationException.php',
         'OC\\DB\\MigrationService' => __DIR__ . '/../../..' . '/lib/private/DB/MigrationService.php',
         'OC\\DB\\Migrator' => __DIR__ . '/../../..' . '/lib/private/DB/Migrator.php',

lib/private/DB/ConnectionFactory.php

@@ -11,6 +11,7 @@ use Doctrine\Common\EventManager;
 use Doctrine\DBAL\Configuration;
 use Doctrine\DBAL\DriverManager;
 use Doctrine\DBAL\Event\Listeners\OracleSessionInit;
+use OC\DB\Middleware\UtcTimezoneMiddleware;
 use OC\DB\QueryBuilder\Sharded\AutoIncrementHandler;
 use OC\DB\QueryBuilder\Sharded\ShardConnectionManager;
 use OC\SystemConfig;
@@ -143,10 +144,14 @@ class ConnectionFactory {
 				$eventManager->addEventSubscriber(new SQLiteSessionInit(true, $journalMode));
 				break;
 		}
+		$configuration = new Configuration();
+		$configuration->setMiddlewares([
+			new UtcTimezoneMiddleware(),
+		]);
 		/** @var Connection $connection */
 		$connection = DriverManager::getConnection(
 			$connectionParams,
-			new Configuration(),
+			$configuration,
 			$eventManager
 		);
 		return $connection;

lib/private/DB/Middleware/UtcTimezoneMiddleware.php

@@ -0,0 +1,25 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2026 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+namespace OC\DB\Middleware;
+
+use Doctrine\DBAL\Driver;
+use Doctrine\DBAL\Driver\Middleware;
+
+/**
+ * Custom doctrine middleware to ensure that the session timezone is set to UTC.
+ *
+ * @since 34.0.0
+ */
+final class UtcTimezoneMiddleware implements Middleware {
+
+	#[\Override]
+	public function wrap(Driver $driver): Driver {
+		return new UtcTimezoneMiddlewareDriver($driver);
+	}
+}

lib/private/DB/Middleware/UtcTimezoneMiddlewareDriver.php

@@ -0,0 +1,39 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2026 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+namespace OC\DB\Middleware;
+
+use Doctrine\DBAL\Driver\Middleware\AbstractDriverMiddleware;
+use Doctrine\DBAL\Platforms\MariaDBPlatform;
+use Doctrine\DBAL\Platforms\MySQLPlatform;
+use Doctrine\DBAL\Platforms\OraclePlatform;
+use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
+
+/**
+ * Driver middleware to ensure the session timezone is set to UTC.
+ * This ensures consistent timezone handling, regardless of server configuration,
+ * similar to how we set the PHP timezone to UTC for Nextcloud.
+ *
+ * @since 34.0.0
+ */
+final class UtcTimezoneMiddlewareDriver extends AbstractDriverMiddleware {
+
+	#[\Override]
+	public function connect(array $params) {
+		$connection = parent::connect($params);
+		$platform = $this->getDatabasePlatform();
+		if (($platform instanceof MariaDBPlatform) || ($platform instanceof MySQLPlatform)) {
+			$connection->exec("SET time_zone = '+00:00'");
+		} elseif ($platform instanceof PostgreSQLPlatform) {
+			$connection->exec("SET TIME ZONE 'UTC'");
+		} elseif ($platform instanceof OraclePlatform) {
+			$connection->exec("ALTER SESSION SET TIME_ZONE='Etc/UTC'");
+		}
+		return $connection;
+	}
+}

tests/lib/DB/Middleware/UtcTimezoneMiddlewareDriverTest.php

@@ -0,0 +1,108 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2026 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+namespace Test\DB\Middleware;
+
+use OC\DB\Connection;
+use OC\DB\ConnectionFactory;
+use OCP\IConfig;
+use OCP\IDBConnection;
+use OCP\Server;
+use PHPUnit\Framework\Attributes\Group;
+use Test\TestCase;
+
+/**
+ * We cannot test the actual driver here,
+ * but we can at least test that it does what we want.
+ */
+#[Group('DB')]
+final class UtcTimezoneMiddlewareDriverTest extends TestCase {
+	private ?Connection $connection = null;
+
+	#[\Override]
+	protected function setUp(): void {
+		parent::setUp();
+		$this->connection = $this->getRootDbConnection();
+		if ($this->connection === null) {
+			$this->markTestSkipped('No root database credentials provided (DB_ROOT_USER, DB_ROOT_PASSWORD), cannot run test');
+			return;
+		}
+
+		$provider = $this->connection->getDatabaseProvider();
+		if ($provider === IDBConnection::PLATFORM_MARIADB || $provider === IDBConnection::PLATFORM_MYSQL) {
+			$this->connection->executeStatement("SET GLOBAL time_zone = 'America/New_York'");
+		} else {
+			$this->markTestSkipped('This test only works with MySQL/MariaDB');
+		}
+	}
+
+	#[\Override]
+	protected function tearDown(): void {
+		if ($this->connection !== null) {
+			$provider = $this->connection->getDatabaseProvider();
+			if ($provider === IDBConnection::PLATFORM_MARIADB || $provider === IDBConnection::PLATFORM_MYSQL) {
+				$this->connection->executeStatement("SET GLOBAL time_zone = 'SYSTEM'");
+			}
+			$this->connection->close();
+		}
+
+		parent::tearDown();
+	}
+
+	public function testSqlNowIsInUtc() {
+		$connection = $this->getDbConnection();
+		$result = $connection->executeQuery('SELECT NOW()');
+		$data = $result->fetchOne();
+		$connection->close();
+
+		self::assertIsString($data, 'Expected a string from the database');
+
+		$expected = new \DateTimeImmutable('now', new \DateTimeZone('UTC'));
+		$received = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $data, new \DateTimeZone('UTC'));
+
+		$diff = abs($received->getTimestamp() - $expected->getTimestamp());
+		self::assertLessThan(15 * 60, $diff); // allow up to 15 minutes of difference, to account for slow test environments and time sync issues.
+	}
+
+	/**
+	 * Get a new database connection.
+	 * This is needed because the setup is changing the global timezone setting,
+	 * but its only applied for new connections.
+	 */
+	private function getDbConnection(array $overrides = []): Connection {
+		$config = Server::get(IConfig::class);
+		$cf = Server::get(ConnectionFactory::class);
+		return $cf->getConnection(
+			$config->getSystemValue('dbtype'),
+			[
+				'host' => $config->getSystemValue('dbhost'),
+				'user' => $config->getSystemValue('dbuser'),
+				'password' => $config->getSystemValue('dbpassword'),
+				'tablePrefix' => $config->getSystemValue('dbtableprefix'),
+				'dbname' => $config->getSystemValue('dbname'),
+				...$overrides,
+			],
+		);
+	}
+	/**
+	 * Get the database connection as root user,
+	 * so that we can change the global timezone setting.
+	 */
+	private function getRootDbConnection(): ?Connection {
+		$rootUser = getenv('DB_ROOT_USER') ?: '';
+		$rootPassword = getenv('DB_ROOT_PASSWORD') ?: '';
+		if ($rootPassword === '' || $rootUser === '') {
+			return null;
+		}
+
+		return $this->getDbConnection([
+			'user' => $rootUser,
+			'password' => $rootPassword,
+		]);
+	}
+}

tests/lib/DB/Middleware/UtcTimezoneMiddlewareTest.php

@@ -0,0 +1,23 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2026 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+namespace Test\DB\Middleware;
+
+use OC\DB\Middleware\UtcTimezoneMiddleware;
+use OC\DB\Middleware\UtcTimezoneMiddlewareDriver;
+use Test\TestCase;
+
+final class UtcTimezoneMiddlewareTest extends TestCase {
+	public function testWrap(): void {
+		$driver = $this->createMock(\Doctrine\DBAL\Driver::class);
+		$middleware = new UtcTimezoneMiddleware();
+		$wrappedDriver = $middleware->wrap($driver);
+
+		$this->assertInstanceOf(UtcTimezoneMiddlewareDriver::class, $wrappedDriver);
+	}
+}