全面测试QtSql操作PostgreSQL数据库时戳字段的行为

全面测试QtSql操作PostgreSQL数据库时戳字段的行为

1. 需求分析

Qt 作为客户端，采用Sql模块操作PostgreSQL数据库的时戳字段时，会遇到不同的操作途径导致的行为差异。具体来说，因为操作系统时区、数据库连接会话时区、字段类型、QDateTime对象时区等因素的不同，会组合出非常多的可能行为。

这些差异因为出现在数据库建立、写入、搬运、比较的各个环节，导致的级联不一致问题会组合出上百钟情况。为了深入理解技术细节导致的结果异同，为现场排查数据不一致性提供全面的测试数据，准备利用本次实验进行一个整理、总结。

2. 测试原理

本测试通过双层模拟，枚举各种可能的搭配。

上层是基于OS的时区配置，每个配置运行1次测试程序，共模拟三个不同的操作系统时区（北京时刻、芝加哥时刻、伦敦时刻）。

下层是分别测试：

• QDateTime时区2种，包括本地时刻（即上层时区）、UTC时刻。
• SQL会话时区3种，包括北京时刻、芝加哥时刻、伦敦时刻。
• SQL绑定方式3种，包括QDateTime对象绑定、无时区字符串绑定、有时区字符串绑定。

双层测试共产生54条记录。

3. 数据库准备

使用 windows 下的 msys2 ucrt64环境自带的 postgresql 18.1版本进行测试。

3.1 运行bash脚本创建数据仓库

initdb -D /d/testdb --locale=C -U postgres psql -dpostgres -Upostgres

3.2 运行sql脚本创建数据库

ALTERUSERpostgresWITHPASSWORD'postgres';createdatabasetest;CREATEROLE testWITHLOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION BYPASSRLS CONNECTIONLIMIT-1PASSWORD'test';CREATEDATABASEtestWITHOWNER=test TEMPLATE=template1ALTERTABLEIFEXISTSpublic.test OWNERtotest;\q

4. 开发测试程序

测试程序基于Qt6.10.1

4.1 项目文件

(1) CMakeLists格式

# 指定 CMake 最低版本要求（Qt 5 最低 3.5，Qt 6 最低 3.16） cmake_minimum_required(VERSION 3.5) # 项目名称和版本（可根据实际情况修改） project(test_pg_timestamp VERSION 1.0.0 LANGUAGES CXX) # 设置 C++ 标准（强制 C++17，与 CONFIG += c++17 对应） set(CMAKE_CXX_STANDARD 17) set(CMAKE_CXX_STANDARD_REQUIRED ON) set(CMAKE_CXX_EXTENSIONS OFF) # 禁用编译器扩展，确保标准一致性 # 查找 Qt 组件（core 和 sql 模块，与 QT = core sql 对应） # 支持 Qt 5 和 Qt 6 自动适配 find_package(Qt6 REQUIRED COMPONENTS Core Sql) if (NOT Qt6_FOUND) find_package(Qt5 5.15 REQUIRED COMPONENTS Core Sql) # Qt5 最低 5.15 以良好支持 C++17 endif() # 设置命令行模式（与 CONFIG += cmdline 对应，不链接 GUI 模块） set(CMAKE_AUTOMOC ON) # 自动处理 Qt 的 moc 元对象编译（Core 模块必需） set(CMAKE_INCLUDE_CURRENT_DIR ON) # 定义可执行文件目标 add_executable(${PROJECT_NAME} main.cpp # 源文件，与 SOURCES += main.cpp 对应 ) # 链接 Qt 库（根据找到的 Qt 版本自动链接） target_link_libraries(${PROJECT_NAME} PRIVATE Qt${QT_VERSION_MAJOR}::Core Qt${QT_VERSION_MAJOR}::Sql )

(2) qmake格式

QT = core sql CONFIG += c++17 cmdline SOURCES += \ main.cpp

4.2 C++代码

#include<QCoreApplication>#include<QSqlDatabase>#include<QSqlQuery>#include<QDateTime>#include<QString>#include<QSqlError>#include<QTimeZone>#include<QTextStream>#include<QSqlField>#include<QSqlRecord>staticQTextStreamsterr(stderr);staticQTextStreamstout(stdout);voidcreate_table(QSqlQuery&query);voidwrite_data(QString name,QDateTime&dtmobj,QSqlQuery&query,boolstr_insert,boolstr_usingtz=true);voidquery_data(QDateTime&dtmobj,QSqlQuery&query,QString field_name,boolstr_insert,boolstr_usingtz=true);voidselect_table(QSqlQuery&query);intmain(intargc,char*argv[]){QCoreApplicationa(argc,argv);constQString tz_os=QTimeZone::systemTimeZone().id();try{QSqlDatabase db=QSqlDatabase::addDatabase("QPSQL","test");if(!db.isValid())throwdb.lastError().text();db.setHostName("localhost");db.setPort(5432);db.setDatabaseName("test");db.setUserName("test");db.setPassword("test");if(!db.open())throwdb.lastError().text();QSqlQueryquery(db);//0. create tablecreate_table(query);//1.1 输入的是本地OS的元旦时刻QDateTime dtm_obj=QDateTime(QDate(2025,01,01),QTime(0,00,00));//1.1.1 数据库连接使用UTC（Qt默认）query.exec("set timezone = 'UTC';");write_data("L0D@"+tz_os,dtm_obj,query,false);//QDateTime对象插入write_data("L0S@"+tz_os,dtm_obj,query,true,false);//不带时区字符串插入write_data("L0Z@"+tz_os,dtm_obj,query,true,true);//携带时区字符串插入//1.1.2 数据库连接使用 Asia/Shanghai +8query.exec("set timezone = 'Asia/Shanghai';");write_data("L8D@"+tz_os,dtm_obj,query,false);//QDateTime对象插入write_data("L8S@"+tz_os,dtm_obj,query,true,false);//不带时区字符串插入write_data("L8Z@"+tz_os,dtm_obj,query,true,true);//携带时区字符串插入//1.1.3 数据库连接使用 America/Chicago -6query.exec("set timezone = 'America/Chicago';");write_data("L6D@"+tz_os,dtm_obj,query,false);//QDateTime对象插入write_data("L6S@"+tz_os,dtm_obj,query,true,false);//不带时区字符串插入write_data("L6Z@"+tz_os,dtm_obj,query,true,true);//携带时区字符串插入query_data(dtm_obj,query,"data_tz",false);query_data(dtm_obj,query,"data_tz",true,false);query_data(dtm_obj,query,"data_tz",true,true);query_data(dtm_obj,query,"data_ntz",false);query_data(dtm_obj,query,"data_ntz",true,false);query_data(dtm_obj,query,"data_ntz",true,true);query_data(dtm_obj,query,"ntz2tz",false);query_data(dtm_obj,query,"ntz2tz",true,false);query_data(dtm_obj,query,"ntz2tz",true,true);query_data(dtm_obj,query,"tz2ntz",false);query_data(dtm_obj,query,"tz2ntz",true,false);query_data(dtm_obj,query,"tz2ntz",true,true);//1.2 输入的是UTC元旦时间dtm_obj=QDateTime(QDate(2025,01,01),QTime(0,00,00),QTimeZone::utc());//1.2.1 数据库连接使用UTC（Qt默认）query.exec("set timezone = 'UTC';");write_data("U0D@"+tz_os,dtm_obj,query,false);//QDateTime对象插入write_data("U0S@"+tz_os,dtm_obj,query,true,false);//不带时区字符串插入write_data("U0Z@"+tz_os,dtm_obj,query,true,true);//携带时区字符串插入//1.2.2 数据库连接使用 Asia/Shanghai +8query.exec("set timezone = 'Asia/Shanghai';");write_data("U8D@"+tz_os,dtm_obj,query,false);//QDateTime对象插入write_data("U8S@"+tz_os,dtm_obj,query,true,false);//不带时区字符串插入write_data("U8Z@"+tz_os,dtm_obj,query,true,true);//携带时区字符串插入//1.2.3 数据库连接使用 America/Chicago -6query.exec("set timezone = 'America/Chicago';");write_data("U6D@"+tz_os,dtm_obj,query,false);//QDateTime对象插入write_data("U6S@"+tz_os,dtm_obj,query,true,false);//不带时区字符串插入write_data("U6Z@"+tz_os,dtm_obj,query,true,true);//携带时区字符串插入query_data(dtm_obj,query,"data_tz",false);query_data(dtm_obj,query,"data_tz",true,false);query_data(dtm_obj,query,"data_tz",true,true);query_data(dtm_obj,query,"data_ntz",false);query_data(dtm_obj,query,"data_ntz",true,false);query_data(dtm_obj,query,"data_ntz",true,true);query_data(dtm_obj,query,"ntz2tz",false);query_data(dtm_obj,query,"ntz2tz",true,false);query_data(dtm_obj,query,"ntz2tz",true,true);query_data(dtm_obj,query,"tz2ntz",false);query_data(dtm_obj,query,"tz2ntz",true,false);query_data(dtm_obj,query,"tz2ntz",true,true);//=========2. select table ====================select_table(query);}catch(QString err){sterr<<"Error! "<<err<<"\n";sterr.flush();}sterr.flush();return0;}voidcreate_table(QSqlQuery&query){constQString sql="CREATE TABLE IF NOT EXISTS public.test_tz "" ( "" test_name character varying(255), "" tz_os character varying(255), "" tz_sql character varying(255), "" tz_src character varying(255), "" src_str character varying(255), "" src_utc bigint, "" opr_type character varying(255), "" data_tz timestamp with time zone, "" data_ntz timestamp without time zone, "" ntz2ts character varying(255), "" ntz2tz timestamp with time zone, "" tz2nts character varying(255), "" tz2ntz timestamp without time zone, "" PRIMARY KEY (test_name) "" );";if(!query.exec(sql))throwquery.lastError().text();}voidwrite_data(QString name,QDateTime&dtmobj,QSqlQuery&query,boolstr_insert,boolstr_usingtz){constQString sql="INSERT INTO public.test_tz ""(test_name, tz_os, tz_sql, tz_src, src_str, "" src_utc,opr_type, data_tz, data_ntz) "" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (test_name) Do NOTHING;";// 获取当前系统时区constQString tz_os=QTimeZone::systemTimeZone().id();// 获取会话时区if(!query.exec("show timezone;"))throwquery.lastError().text();if(!query.next())throwQString("\nError. Can not get session timezone.\n");constQString tz_sql=query.value(0).toString();//获取来源时区、来源的字符串constQString tz_src=dtmobj.timeZone().id();constQString src_str=str_usingtz?dtmobj.toString("yyyy-MM-ddTHH:mm:ss.zzzt"):dtmobj.toString("yyyy-MM-dd HH:mm:ss.zzz");constqint64 src_utc=dtmobj.toSecsSinceEpoch();//Insertif(!query.prepare(sql))throwquery.lastError().text();query.addBindValue(name);query.addBindValue(tz_os);query.addBindValue(tz_sql);query.addBindValue(tz_src);query.addBindValue(src_str);query.addBindValue(src_utc);if(str_insert){query.addBindValue(str_usingtz?"QString+zone":"QString");query.addBindValue(src_str);query.addBindValue(src_str);}else{query.addBindValue("QDateTime");query.addBindValue(dtmobj);query.addBindValue(dtmobj);}if(!query.exec())throwquery.lastError().text();if(true){//read and mov data tz->ntzif(!query.prepare("select data_tz from public.test_tz where test_name = ?;"))throwquery.lastError().text();query.addBindValue(name);if(!query.exec())throwquery.lastError().text();if(!query.next())throwQString("\nError. Can not get data_tz.\n");constQDateTime dtmVal=query.value(0).toDateTime();constQString textDate=str_usingtz?query.value(0).toString():dtmVal.toString("yyyy-MM-dd HH:mm:ss.zzz");if(!query.prepare("update public.test_tz set tz2nts = ?, tz2ntz = ? where test_name = ?;"))throwquery.lastError().text();query.addBindValue(textDate);if(str_insert)query.addBindValue(textDate);elsequery.addBindValue(dtmVal);query.addBindValue(name);if(!query.exec())throwquery.lastError().text();}if(true){//read and mov data ntz->tzif(!query.prepare("select data_ntz from public.test_tz where test_name = ?;"))throwquery.lastError().text();query.addBindValue(name);if(!query.exec())throwquery.lastError().text();if(!query.next())throwQString("\nError. Can not get data_ntz.\n");constQDateTime dtmVal=query.value(0).toDateTime();constQString textDate=str_usingtz?query.value(0).toString():dtmVal.toString("yyyy-MM-dd HH:mm:ss.zzz");if(!query.prepare("update public.test_tz set ntz2ts = ?, ntz2tz = ? where test_name = ?;"))throwquery.lastError().text();query.addBindValue(textDate);if(str_insert)query.addBindValue(textDate);elsequery.addBindValue(dtmVal);query.addBindValue(name);if(!query.exec())throwquery.lastError().text();}}voidselect_table(QSqlQuery&query){constQString sql="select * from public.test_tz order by test_name ASC;";stout<<"\n"<<sql<<"\n";if(!query.exec(sql))throwquery.lastError().text();inti=0;while(query.next()){constintcount=query.record().count();if(i==0){stout<<"\n\n|";for(inti=0;i<count;++i)stout<<query.record().fieldName(i)<<"|";stout<<"\n|";for(inti=0;i<count;++i)stout<<"---"<<"|";}stout<<"\n|";for(inti=0;i<count;++i){stout<<query.record().value(i).toString();stout<<"|";}stout.flush();++i;}stout<<"\n";stout.flush();}voidquery_data(QDateTime&dtmobj,QSqlQuery&query,QString field_name,boolusingstr,boolstr_usingtz){constQString sql=QString("select test_name, %1 from public.test_tz where ""%1 = ? order by test_name asc;").arg(field_name);stout<<"\n"<<sql<<"\n";// 获取当前系统时区constQString tz_os=QTimeZone::systemTimeZone().id();// 获取会话时区if(!query.exec("show timezone;"))throwquery.lastError().text();if(!query.next())throwQString("\nError. Can not get session timezone.\n");constQString tz_sql=query.value(0).toString();//获取来源时区、来源的字符串constQString tz_src=dtmobj.timeZone().id();constQString src_str=str_usingtz?dtmobj.toString("yyyy-MM-ddTHH:mm:ss.zzzt"):dtmobj.toString("yyyy-MM-dd HH:mm:ss.zzz");constqint64 src_utc=dtmobj.toSecsSinceEpoch();stout<<"OS = "<<tz_os<<", Session = "<<tz_sql<<", QDateTime = "<<tz_src<<",";stout<<" Query "<<field_name<<" using "<<src_str<<" , utc = "<<src_utc<<", operation = ";//selectif(!query.prepare(sql))throwquery.lastError().text();if(usingstr){stout<<(str_usingtz?"QString+zone":"QString");query.addBindValue(src_str);}else{stout<<"QDateTime object";query.addBindValue(dtmobj);}if(!query.exec())throwquery.lastError().text();inti=0;while(query.next()){constintcount=query.record().count();if(i==0){stout<<"\n\n|";for(inti=0;i<count;++i)stout<<query.record().fieldName(i)<<"|";stout<<"\n|";for(inti=0;i<count;++i)stout<<"---"<<"|";}stout<<"\n|";for(inti=0;i<count;++i){stout<<query.record().value(i).toString();stout<<"|";}stout.flush();++i;}stout<<"\n\n";}

5. 产生数据内容

分别设置操作系统时区为北京时刻、芝加哥时刻、伦敦时刻，反复运行上述程序3次，得到完整的数据表如下：

6. 结果详细分析

通过对测试数据的梳理和验证，QtSql操作PostgreSQL时戳字段的行为受多重因素影响，主要规律如下：

6.1 核心影响因素及作用机制

1. 时区层级的优先级
   时戳处理中存在三级时区影响，优先级从高到低为：

   • 字符串插入时显式携带的时区（如2025-01-01T00:00:00.000+08:00）；
   • QDateTime对象自身绑定的时区（通过QDateTime(..., QTimeZone)指定）；
   • 数据库会话时区（通过set timezone = 'xxx'设置）。
     操作系统时区（tz_os）仅影响未指定时区的QDateTime对象的默认时区（此时QDateTime默认使用系统时区）。

2. 插入操作的行为差异

   • QDateTime对象插入：Qt会自动将对象转换为数据库会话时区对应的时戳存储。例如，当会话时区为America/Chicago（UTC-6）时，插入一个UTC时区的2025-01-01 00:00:00，数据库会存储为2024-12-31 18:00:00-06:00（带时区字段）或2024-12-31 18:00:00（不带时区字段）。
   • 字符串插入（不带时区）：数据库会按当前会话时区解析字符串。例如，会话时区为Asia/Shanghai（UTC+8）时，插入字符串2025-01-01 00:00:00，会被解析为UTC+8的该时刻，对应UTC时间为2024-12-31 16:00:00。
   • 字符串插入（带时区）：数据库直接按字符串中的时区解析，不受会话时区影响。例如，插入2025-01-01T00:00:00.000UTC，无论会话时区如何，均解析为UTC时间的该时刻。

3. 字段类型的存储特性

   • timestamp with time zone（data_tz）：存储时会转换为UTC时间，读取时根据会话时区转换为当地时间显示。例如，存储的UTC时间2025-01-01 00:00:00，在Asia/Shanghai会话时区下读取显示为2025-01-01 08:00:00+08:00。
   • timestamp without time zone（data_ntz）：直接存储字符串或QDateTime转换后的本地时间（不含时区信息），读取时不会进行时区转换。例如，在America/Chicago会话时插入的2025-01-01 00:00:00，读取时仍为该值，与会话时区无关。

4. 时戳搬运（字段间转换）的规律

   • data_tz转tz2ntz（带时区转不带时区）：会将带时区时戳按当前会话时区转换为本地时间，作为不带时区时戳存储。
   • data_ntz转ntz2tz（不带时区转带时区）：会将不带时区时戳按当前会话时区解析为带时区时戳（即假设该时间为会话时区的本地时间）。

6.2 常见问题与风险点

• 歧义性插入：使用不带时区的字符串插入data_ntz字段时，若会话时区变化，相同字符串会被解析为不同UTC时间，导致数据不一致。例如，同样插入2025-01-01 00:00:00，在UTC和Asia/Shanghai会话时区下，对应UTC时间相差8小时。
• 时区丢失：data_ntz字段存储时丢失时区信息，后续读取或转换时需依赖固定会话时区，否则可能出现逻辑错误（如跨时区查询时时间对比失准）。
• Qt版本差异：测试基于Qt6.10.1，低版本Qt（如Qt5.15以下）可能存在时区转换逻辑差异，需额外验证。

6.3 query日期比较的行为规律与结果差异解析

查询时的日期比较结果（能否匹配到记录）受查询条件格式、字段类型、会话时区及插入时的时区处理逻辑共同影响。结合测试数据，具体规律如下：

6.3.1 能查询到结果的典型场景及原因

1. 查询条件与字段存储的UTC时间一致（带时区字段）
   对于data_tz（timestamp with time zone）字段，其底层存储为UTC时间，查询时会根据会话时区转换为本地时间进行匹配。当查询条件（如QDateTime对象或带时区的字符串）解析后的UTC时间与存储的UTC时间一致时，可匹配到结果。

   • 例：在“计算机为北京时间，Session=America/Chicago，查询data_tz使用QDateTime=UTC（2025-01-01 00:00:00）”的场景中，L0D@America/Chicago的data_tz字段存储的UTC时间为2025-01-01 06:00:00（对应会话时区-06:00的本地时间2025-01-01 00:00:00），与查询条件的UTC时间一致，因此被匹配到（见代码片段中L0D@America/Chicago的查询结果）。

2. 查询条件与字段存储的本地时间一致（不带时区字段）
   对于data_ntz（timestamp without time zone）字段，其存储的是插入时的本地时间（无时区信息）。当查询条件的本地时间（按会话时区解析）与存储的本地时间一致时，可匹配到结果。

   • 例：多个测试用例中，data_ntz字段均存储为2025-01-01T00:00:00.000Z（本地时间），无论查询时的会话时区是Asia/Shanghai、America/Chicago还是UTC，只要查询条件解析后的本地时间为2025-01-01 00:00:00，即可匹配到所有相关记录（如代码片段中多次出现的data_ntz查询结果）。

3. 转换字段（tz2ntz/ntz2tz）的转换逻辑匹配

   • tz2ntz字段由data_tz按会话时区转换为本地时间存储（无时区）。当查询tz2ntz时，若条件为该本地时间（如2025-01-01T00:00:00.000Z），则所有符合转换逻辑的记录均可匹配（见“计算机为芝加哥时间”的tz2ntz查询结果）。
   • ntz2tz字段由data_ntz按会话时区解析为带时区时间存储。当查询ntz2ntz时，若条件的UTC时间与解析后的UTC时间一致，则可匹配（如代码片段中ntz2tz查询结果均为2024-12-31T18:00:00.000-06:00，对应UTC时间一致）。

6.3.2 查不到结果的典型场景及原因

1. 查询条件与字段的时区解析逻辑冲突

   • 对data_tz字段使用不带时区的字符串查询时，字符串会按当前会话时区解析为UTC时间。若该UTC时间与字段存储的UTC时间不一致，则无法匹配。
     例：若data_tz存储的UTC时间为2025-01-01 06:00:00（对应会话时区America/Chicago的本地时间2025-01-01 00:00:00），若查询时会话时区改为Asia/Shanghai，不带时区的查询条件2025-01-01 00:00:00会被解析为UTC时间2024-12-31 16:00:00，与存储值不一致，导致查不到结果。

2. 不带时区字段的“隐性时区依赖”不匹配
   data_ntz字段存储时丢失时区信息，查询时依赖会话时区解析条件。若查询时的会话时区与插入时的会话时区不同，相同字符串条件会被解析为不同本地时间，导致不匹配。

   • 例：插入data_ntz时会话时区为UTC，存储本地时间2025-01-01 00:00:00；若查询时会话时区为Asia/Shanghai，查询条件2025-01-01 00:00:00会被解析为UTC+8的本地时间（对应UTC时间2024-12-31 16:00:00），与存储的本地时间（UTC时区）不一致，无法匹配。

3. 转换字段的转换逻辑未被正确适配

   • tz2ntz字段的值取决于data_tz转换时的会话时区。若查询时使用的条件未对应转换时的本地时间，则无法匹配。例如，若tz2ntz是data_tz在UTC时区下转换的本地时间（2025-01-01 06:00:00），而查询条件使用2025-01-01 00:00:00，则无法匹配。
   • ntz2tz字段的值取决于data_ntz解析时的会话时区。若查询条件的时区与解析时的会话时区不同，导致UTC时间不匹配，则查不到结果。

6.3.3 核心结论

查询能否匹配的本质是查询条件解析后的UTC时间（带时区字段）或本地时间（不带时区字段）与存储值是否一致。带时区字段的查询依赖“UTC时间对齐”，不带时区字段的查询依赖“本地时间对齐+会话时区一致”，转换字段则需同时满足原始字段的处理逻辑和转换时的时区规则。

7. 编程最佳规范建议

基于上述测试结果，为避免QtSql操作PostgreSQL时戳字段出现一致性问题，建议遵循以下规范：

7.1 字段类型选择

• 优先使用timestamp with time zone：该类型自带时区信息，存储和读取时会自动进行时区转换，能最大程度避免跨时区场景下的歧义。仅在明确无需时区信息（如纯本地时间记录）时使用timestamp without time zone。

7.2 插入操作规范

• 推荐使用QDateTime对象插入：创建QDateTime时需显式指定时区（如QDateTime(..., QTimeZone::utc())），避免依赖系统默认时区。例如：

  QDateTimedtm(QDate(2025,1,1),QTime(0,0,0),QTimeZone::utc());// 明确UTC时区query.addBindValue(dtm);// 绑定到data_tz字段

• 避免使用不带时区的字符串插入：若必须使用字符串，需携带明确时区信息（如yyyy-MM-ddTHH:mm:ss.zzzt格式），例如：

  QString str=dtm.toString("yyyy-MM-ddTHH:mm:ss.zzzt");// 含时区的字符串query.addBindValue(str);

7.3 数据库会话配置

• 统一数据库会话时区为UTC：连接数据库后立即执行set timezone = 'UTC';，避免会话时区变化导致的转换差异。例如：

  QSqlQueryquery(db);query.exec("set timezone = 'UTC';");// 固定会话时区为UTC

• 统一数据库会话时区为约定时区：如连接数据库后立即执行set timezone = 'Asia/Shanghai';，避免会话时区变化导致的转换差异。例如：

  QSqlQueryquery(db);query.exec("set timezone = 'Asia/Shanghai';");// 固定会话时区为Asia/Shanghai

7.4 读取与转换操作

• 读取时显式指定目标时区：从data_tz字段读取QDateTime后，可通过toTimeZone()转换为业务所需时区，避免依赖默认时区。例如：

  QDateTime dtm=query.value(0).toDateTime();QDateTime shanghaiTime=dtm.toTimeZone(QTimeZone("Asia/Shanghai"));// 转换为上海时区

• data_ntz字段处理：读取时需明确其原始插入时的会话时区，必要时通过QDateTime::fromString手动指定时区解析，例如：

  QString ntzStr=query.value(0).toString();QDateTime dtm=QDateTime::fromString(ntzStr,"yyyy-MM-dd HH:mm:ss.zzz");dtm.setTimeZone(QTimeZone("UTC"));// 假设原始插入时区为UTC

7.5 测试与验证

• 覆盖多时区场景：测试用例需包含至少3种时区（如UTC、Asia/Shanghai、America/Chicago），验证插入、读取、转换的一致性。
• 对比UTC时间戳：通过QDateTime::toSecsSinceEpoch()将时戳转换为UTC秒数进行校验，确保不同操作最终对应相同的UTC时刻。

遵循以上规范可有效减少因时区、字段类型、插入方式导致的时戳不一致问题，确保QtSql操作PostgreSQL时戳字段的可靠性。

7.6 查询操作规范

1. 带时区字段（data_tz）查询

   • 优先使用带时区的QDateTime对象或含时区的字符串（如yyyy-MM-ddTHH:mm:ss.zzzt）作为条件，确保查询条件的UTC时间与存储值一致。
     示例：

     QDateTimequeryDt(QDate(2025,1,1),QTime(0,0,0),QTimeZone::utc());// 明确UTC时区query.prepare("select * from test_tz where data_tz = ?");query.addBindValue(queryDt);

   • 避免使用不带时区的字符串，若必须使用，需确保会话时区与插入时一致。

2. 不带时区字段（data_ntz）查询

   • 必须明确字段插入时的会话时区，查询时使用相同会话时区，并以本地时间字符串或绑定对应时区的QDateTime作为条件（需手动转换为插入时的本地时间）。
     示例（假设插入时会话时区为UTC）：

     QDateTimequeryDt(QDate(2025,1,1),QTime(0,0,0),QTimeZone::utc());// 插入时的本地时间query.prepare("select * from test_tz where data_ntz = ?");query.addBindValue(queryDt.toString("yyyy-MM-dd HH:mm:ss.zzz"));// 按本地时间字符串查询

3. 统一查询时区上下文
   执行查询前固定会话时区（如set timezone = 'UTC'），并确保查询条件的时区处理与该上下文一致，避免跨查询的时区波动导致结果不一致。

遵循以上查询规范可有效减少因条件格式、时区解析差异导致的“查不到结果”问题，确保日期比较的准确性。

附件 query_data 历次输出结果

(1) 计算机为北京时间

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query data_tz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QDateTime object

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query data_tz using 2025-01-01 00:00:00.000 , utc = 1735660800, operation = QString

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query data_tz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QString+zone

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query data_ntz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QDateTime object

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query data_ntz using 2025-01-01 00:00:00.000 , utc = 1735660800, operation = QString

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query data_ntz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QString+zone

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query ntz2tz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QDateTime object

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query ntz2tz using 2025-01-01 00:00:00.000 , utc = 1735660800, operation = QString

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query ntz2tz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QString+zone

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query tz2ntz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QDateTime object

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query tz2ntz using 2025-01-01 00:00:00.000 , utc = 1735660800, operation = QString

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = Asia/Shanghai, Query tz2ntz using 2025-01-01T00:00:00.000GMT+8 , utc = 1735660800, operation = QString+zone

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query data_tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query data_tz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query data_tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query data_ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query data_ntz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query data_ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query ntz2tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query ntz2tz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query ntz2tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query tz2ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query tz2ntz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Asia/Shanghai, Session = America/Chicago, QDateTime = UTC, Query tz2ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

(2) 计算机为UTC时间

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query data_tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query data_tz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query data_tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query data_ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query data_ntz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query data_ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query ntz2tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query ntz2tz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query ntz2tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query tz2ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query tz2ntz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = Etc/UTC, Query tz2ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query data_tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query data_tz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query data_tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query data_ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query data_ntz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query data_ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query ntz2tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query ntz2tz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, ntz2tz from public.test_tz where ntz2tz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query ntz2tz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query tz2ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QDateTime object

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query tz2ntz using 2025-01-01 00:00:00.000 , utc = 1735689600, operation = QString

select test_name, tz2ntz from public.test_tz where tz2ntz = ? order by test_name asc;
OS = Etc/UTC, Session = America/Chicago, QDateTime = UTC, Query tz2ntz using 2025-01-01T00:00:00.000UTC , utc = 1735689600, operation = QString+zone

(3) 计算机为芝加哥时间

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = America/Chicago, Session = America/Chicago, QDateTime = America/Chicago, Query data_tz using 2025-01-01T00:00:00.000GMT-6 , utc = 1735711200, operation = QDateTime object

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = America/Chicago, Session = America/Chicago, QDateTime = America/Chicago, Query data_tz using 2025-01-01 00:00:00.000 , utc = 1735711200, operation = QString

select test_name, data_tz from public.test_tz where data_tz = ? order by test_name asc;
OS = America/Chicago, Session = America/Chicago, QDateTime = America/Chicago, Query data_tz using 2025-01-01T00:00:00.000GMT-6 , utc = 1735711200, operation = QString+zone

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = America/Chicago, Session = America/Chicago, QDateTime = America/Chicago, Query data_ntz using 2025-01-01T00:00:00.000GMT-6 , utc = 1735711200, operation = QDateTime object

select test_name, data_ntz from public.test_tz where data_ntz = ? order by test_name asc;
OS = America/Chicago, Session = America/Chicago, QDateTime = America/Chicago, Query data_ntz using 2025-01-01 00:00:00.000 , utc = 1735711200, operation = QString