修复三类数据查询的数据库时间转换

yeg

2026-09-06 ac360fe22ae21722c3af52ef727c8456f5f100e7

修复三类数据查询的数据库时间转换

将数据库返回的 LocalDateTime 按 UTC 转换为毫秒时间戳，避免历史数据展示和图表时间偏移。补充控制台时间契约及真实 MySQL 导入查询回归，验证原始文本与平台接口保持既有语义。

 battery-business/src/main/java/com/battery/business/service/impl/TestDataQueryServiceImpl.java

@@ -1,6 +1,8 @@
package com.battery.business.service.impl;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Date;
import java.util.List;
import java.util.Map;
@@ -52,9 +54,11 @@
    private List<Map<String, Object>> normalizeApiTimes(List<Map<String, Object>> rows)
    {
        if (rows == null) return List.of();
        // DATETIME(3) 保存 UTC，JDBC 返回的无时区值也必须按 UTC 转换，不能采用服务器默认时区。
        for (Map<String, Object> row : rows)
            row.replaceAll((key, value) -> value instanceof Date date ? date.getTime()
                    : value instanceof Instant instant ? instant.toEpochMilli() : value);
                    : value instanceof Instant instant ? instant.toEpochMilli()
                    : value instanceof LocalDateTime time ? time.toInstant(ZoneOffset.UTC).toEpochMilli() : value);
        return rows;
    }
}

 ruoyi-admin/src/test/java/com/battery/business/runtime/ConsoleTimeContractTest.java

@@ -2,7 +2,12 @@

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;
import java.time.Instant;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.junit.jupiter.api.Test;
@@ -12,6 +17,11 @@
import com.battery.business.domain.TTest;
import com.battery.business.domain.TTestDataRevisionAction;
import com.battery.business.service.ITestLiveService.LiveState;
import com.battery.business.mapper.TTestMapper;
import com.battery.business.service.impl.TTestServiceImpl;
import com.battery.business.service.impl.TestDataQueryServiceImpl;
import com.battery.business.web.TTestController;
import org.springframework.test.util.ReflectionTestUtils;

/** 控制台 REST/SSE 的业务时间统一为毫秒，平台响应保持原有格式。 */
class ConsoleTimeContractTest
@@ -48,4 +58,40 @@
        assertEquals(time, json.readTree(json.writeValueAsString(state)).get("updatedAt").longValue());
        assertEquals(time, json.readTree(json.writeValueAsString(Map.of("at", state.updatedAt()))).get("at").longValue());
    }

    @Test
    void persistedDataEndpointsInterpretDatabaseLocalDateTimeAsUtcMillis()
    {
        var mapper = mock(TTestMapper.class);
        var storedTest = new TTest(); storedTest.setTestId(42L);
        when(mapper.selectTestById(42L)).thenReturn(storedTest);
        var tests = new TTestServiceImpl();
        ReflectionTestUtils.setField(tests, "testMapper", mapper);
        var controller = new TTestController();
        ReflectionTestUtils.setField(controller, "testDataQueryService", new TestDataQueryServiceImpl(mapper, tests));
        // JDBC 可将 UTC DATETIME(3) 返回为 LocalDateTime；它不是用户所在时区的本地时间。
        for (Object databaseTime : List.of(LocalDateTime.parse("2023-05-08T03:42:11.123"),
                Instant.parse("2023-05-08T03:42:11.123Z"), new Date(1683517331123L)))
        {
            Map<String, Object> row = new LinkedHashMap<>();
            for (String field : List.of("deviceTime", "collectorDeviceTime", "captureSlot", "serverReceivedAt", "recordedAt", "absoluteTime"))
                row.put(field, databaseTime);
            row.put("deviceTimeRaw", "2023-05-08 11:42:11.123");
            row.put("temperature", null);
            when(mapper.selectPersistedFinalRealtimeRecords(42L)).thenReturn(List.of(new LinkedHashMap<>(row)));
            when(mapper.selectPersistedFinalStepRecords(42L)).thenReturn(List.of(new LinkedHashMap<>(row)));
            when(mapper.selectPersistedFinalCycleRecords(42L)).thenReturn(List.of(new LinkedHashMap<>(row)));
            for (AjaxResult result : List.of(controller.persistedRecords(42L), controller.persistedSteps(42L), controller.persistedCycles(42L)))
            {
                var item = json.readTree(json.writeValueAsString(result)).get("data").get(0);
                for (String field : List.of("deviceTime", "collectorDeviceTime", "captureSlot", "serverReceivedAt", "recordedAt", "absoluteTime"))
                {
                    assertTrue(item.get(field).isIntegralNumber(), field + ": " + item.get(field));
                    assertEquals(1683517331123L, item.get(field).longValue());
                }
                assertEquals("2023-05-08 11:42:11.123", item.get("deviceTimeRaw").asString());
                assertTrue(item.get("temperature").isNull());
            }
        }
    }
}

 ruoyi-admin/src/test/java/com/battery/business/web/ConsoleWorkbookImportMysqlTest.java

@@ -12,6 +12,8 @@
import com.battery.business.mapper.TTestMapper;
import com.battery.business.service.ITestConsoleDataService;
import com.battery.business.service.impl.TestConsoleDataServiceImpl;
import com.battery.business.service.impl.TTestServiceImpl;
import com.battery.business.service.impl.TestDataQueryServiceImpl;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.Test;
@@ -67,6 +69,9 @@
                    @Override public String getUsername() { return "验收操作员"; }
                };
                ReflectionTestUtils.setField(controller, "consoleDataService", service);
                var tests = new TTestServiceImpl();
                ReflectionTestUtils.setField(tests, "testMapper", mapper);
                ReflectionTestUtils.setField(controller, "testDataQueryService", new TestDataQueryServiceImpl(mapper, tests));
                var sample = JSON.parseObject(Files.readString(Path.of(System.getenv("TICKET03_SAMPLE_JSON"))));
                Map<String, List<Map<String, Object>>> sheets = new LinkedHashMap<>();
                for (String type : List.of("REALTIME", "STEP", "CYCLE")) sheets.put(type, (List) sample.getJSONArray(type).toJavaList(Map.class));
@@ -83,6 +88,19 @@
                assertEquals(7605, mapper.selectPersistedFinalRealtimeRecords(1L).size());
                assertEquals(129, mapper.selectPersistedFinalStepRecords(1L).size());
                assertEquals(33, mapper.selectPersistedFinalCycleRecords(1L).size());
                // 通过正式查询入口验证真实 JDBC 时间类型，避免内存库时间类型掩盖接口偏差。
                var json = tools.jackson.databind.json.JsonMapper.builder().findAndAddModules().build();
                for (var result : List.of(controller.persistedRecords(1L), controller.persistedSteps(1L), controller.persistedCycles(1L)))
                {
                    var rows = json.readTree(json.writeValueAsString(result)).get("data");
                    for (var row : rows) for (String field : List.of("deviceTime", "recordedAt", "absoluteTime", "serverReceivedAt"))
                    {
                        var value = row.get(field);
                        if (value != null && !value.isNull()) assertTrue(value.isIntegralNumber(), field + ": " + value);
                    }
                }
                var firstRecord = json.readTree(json.writeValueAsString(controller.persistedRecords(1L))).get("data").get(7604);
                assertEquals(1683517331000L, firstRecord.get("deviceTime").longValue());
                var actions = service.selectActions(new TTestDataRevisionAction());
                assertEquals(1, actions.size());
                var revision = service.selectActionById(actions.get(0).getActionId());