文章目录1、问题引入2、时间解析2.1 什么是 UTC 时间2.1.1 UTC 是全球统一的时间基准2.1.2 UTC 和 GMT 的关系2.2 什么是北京时间CST2.2.1 北京时间没有夏令时切换2.3 什么是 PST 和 PDT2.3.1 PSTPacific Standard Time2.3.2 PDTPacific Daylight Time2.3.3 PST 与 PDT 的关系2.4 PST/PDT 与北京时间相差多少3、跨时区项目一般怎么处理原则一后端和数据层统一使用 UTC原则二展示时才转成用户当地时间4、我的项目怎么处理4.1 查看MySQL Server 和 Session 当前使用的时区4.2 查看命名时区表是否已经加载4.3 开启时区数据库4.3.1 windows系统4.3.2 Linux系统4.3.4 云数据库4.3.3 验证我的网站原文 https://eleanora-lyh.github.io/MyLearningNotes/csdn处的文章会尽快同步更新欢迎大家来访问1、问题引入先介绍一下上游数据L1事件明细记录了Web 侧事件级遥测数据包含用户行为及部分系统/测试/机器人事件。此文件每一个小时记录会生成一个每个大小为5-30TB左右。假设当前时间为curr_time我们需要得到curr_time-2h, curr_time-1h, curr_time, curr_time1h共计四个文件才能开始当前时间为curr_time的计算任务。每两个小时开始一个这样的计算任务也就是说一天需要完成12个并生成对应12个文件每个计算任务完成都会在数据库插入一条记录。目前的保障措施因为上游数据数据达到的时间不确定所以如果想保证计算任务顺利执行在设计ETL流程时会根据当前时间预判可能的到达时间并wait10h在此期间如果需要的所有上游文件都到达就会触发计算任务。同时增加了对上游数据的监控在规定时间范围内未到达就会触发通知。但这样做还是有一个遗漏之处就是是上游文件在规定时间范围内到达但是由于计算任务时间过长或者计算量太大就会导致计算任务无法准时完成。而目前监控计算任务是否完成只能通过肉眼去查文件是否生成。因此引入我的任务在grafana中新建一个dashboard来可视化计算任务的结果用来判断计算任务是否完成实现过程也很简单去查对应时间的数据库内是否有计算任务完成的记录构造本应在一天中生成的12个文件行将其与数据库进行Join对的上的标记为存在对不上的标记为不存在。在实现的过程中我发现自己踩了时区转化的坑导致dashboard中展示的计算结果的create_time和实际create_time不同具体踩坑点如下grafana的dashboard中有时间范围筛选其默认时区为北京时间Mysql数据库的时区为UTC时间计算任务生成的结果存储在Azure云上其时区为PDT时间这样就导致了同一条记录在不同的地方查询显示/操作的create_time都是不同的所以此文章介绍一下时区转换2、时间解析2.1 什么是 UTC 时间2.1.1 UTC 是全球统一的时间基准UTC 全称Coordinated Universal Time协调世界时它不是某个国家或城市的地方时间而是全球系统进行时间同步时使用的基准。常见表示方式2026-07-31T06:30:00Z2026-07-31日期T日期和时间的分隔符06:30:00时、分、秒Z表示 UTC也叫 Zulu Time2.1.2 UTC 和 GMT 的关系日常业务中UTC0和GMT0两者通常可以视为相同偏移但概念上并不完全相同UTCCoordinated Universal Time现代时间标准基于原子钟等时间体系。GMT Greenwich Mean Time格林尼治平均时最初基于天文观测也常被用作时区名称。对于日志、数据库、API、Pipeline 和告警系统建议统一使用UTC而不是 GMT。2.2 什么是北京时间CST北京时间CST通常指China Standard TimeCST常表示为 UTC08:00换算关系UTC 2026-07-31 16:30对应北京时间 2026-08-01 00:30注意减去或加上 8 小时后日期也可能变化2.2.1 北京时间没有夏令时切换目前中国标准时间全年固定为UTC8因此在当前规则下夏天是 UTC8冬天也是 UTC8不存在北京时间突然跳过一小时或重复一小时的问题不过跨时区项目中不要把它简单写成模糊的CST。因为CST可能被解释为China Standard TimeCentral Standard TimeCuba Standard Time更明确的写法是UTC08:00和Asia/Shanghai2.3 什么是 PST 和 PDTPST 和 PDT 都与北美太平洋时区有关他们是北美太平洋时区在不同月份范围的细分时区但是可以统称为Pacific Time缩写为PT即PT PST 或 PDT按美国现行的一般规则夏令时从每年 3 月第二个星期日开始到 11 月第一个星期日结束。这个时间范围内采用PDT时区剩余时间范围采用PST时区。2.3.1 PSTPacific Standard Time太平洋标准时间 Pacific Standard Time 缩写PST主要对应不使用夏令时(DST)的时期UTC 偏移UTC-8比 UTC 慢 8 小时使用地区美国西海岸加州、华盛顿州、俄勒冈州、内华达州等、加拿大不列颠哥伦比亚省主要城市包括洛杉矶、旧金山、西雅图、波特兰、温哥华启用时段每年11 月第一个星期日到次年3 月第二个星期日即冬令时夏令时切换3 月第二个星期日起切换为PDTUTC-711 月第一个星期日回切到 PST2.3.2 PDTPacific Daylight Time太平洋夏令时间 Pacific Daylight TimePDT 在实行夏令时期间使用UTC 偏移PDT UTC-7比 UTC 晚 7 小时使用地区美国西海岸加州、华盛顿州、内华达州、俄勒冈州等、加拿大不列颠哥伦比亚省主要城市包括洛杉矶、旧金山、西雅图、波特兰、温哥华等启用时段每年3 月第二个星期日到11 月第一个星期日冬令时切换夏令时结束后切换回PSTPacific Standard Time太平洋标准时间UTC-8 注意PDT 只在夏令时期间有效3 月中11 月初。其余月份美国西海岸用的是 PSTUTC-8。如果你的业务时间跨度覆盖全年不能简单地固定减 7 小时应该用命名时区让 MySQL 自动处理切换CONVERT_TZ(utc_time,00:00,America/Los_Angeles)2.3.3 PST 与 PDT 的关系两者指的是同一个地理区域的同一条时间线只是根据季节在标准时间和夏令时之间切换时段缩写全称UTC 偏移冬季11 月3 月PSTPacificStandard TimeUTC-8夏季3 月11 月PDTPacificDaylight TimeUTC-7也就是说PDT PST 1 小时。夏令时开始时时钟拨快 1 小时PST → PDT结束时拨慢 1 小时PDT → PST。 所以严格来说Pacific TimePT是一个统称它底下分 PST 和 PDT 两种状态随季节切换。2.4 PST/PDT 与北京时间相差多少已知PDT UTC-7PST UTC-8北京时间 UTC8所以可以得出太平洋当地时间UTC 偏移与北京时间的时差PSTUTC-8CSTPST16HPDTUTC-7CSTPST15H3、跨时区项目一般怎么处理原则一后端和数据层统一使用 UTC建议以下内容全部保存为 UTC数据库时间字段Pipeline 的运行时间文件到达时间日志时间API 请求时间事件发生时间告警检测时间消息队列中的时间分布式系统 Watermark审计记录例如2026-07-31T06:30:00Z而不是只保存2026-07-31 14:30:00后者没有说明时区无法判断它是北京时间、UTC 还是美国当地时间。原则二展示时才转成用户当地时间典型流程是事件发生 ↓ 转换为 UTC ↓ 数据库按 UTC 保存 ↓ API 返回 UTC ↓ 前端根据用户时区显示4、我的项目怎么处理因为想的是替换掉手动去云存储那里一个个查文件的过程所以dashboard这里显示的时间要和云存储的create_time一致MySQL create_time UTC 云存储 create_time 洛杉矶当地时间针对目标查询表加一层view视图将转为洛杉矶当地时间如下CONVERT_TZ(create_time,00:00,America/Los_Angeles)如果转换完得到的时间为NULL是因为MySQL 必须查询内部时区表以确定America/Los_Angeles是否存在2026 年 7 月 31 日是否处于夏令时此时应该使用 UTC-7 还是 UTC-8夏令时切换边界如何处理当前 MySQL 查不到对应的时区规则因此返回NULL。解决办法详见下面的4.1、4.2、4.34.1 查看MySQL Server 和 Session 当前使用的时区SELECTsystem_time_zoneASsystem_time_zone,global.time_zoneASglobal_time_zone,session.time_zoneASsession_time_zone;返回system_time_zoneglobal_time_zonesession_time_zoneChina Standard TimeSYSTEMSYSTEM4.2 查看命名时区表是否已经加载SELECTCOUNT(*)AStimezone_countFROMmysql.time_zone_nameWHERENameAmerica/Los_Angeles;返回timezone_count04.3 开启时区数据库要在 MySQL 中使用命名时区如America/Los_Angeles、Asia/Shanghai必须先把系统的IANA 时区数据库zoneinfo加载进 MySQL 自带的mysql.time_zone_*系统表里。否则CONVERT_TZ用命名时区时会返回NULL。4.3.1 windows系统windows系统则首先下载 MySQL 时区包MySQL :: Time zone description tables选择timezone_2026c_posix_sql.zip解压到对应位置我的位置是C:\Users\v-liuyuhang\AppData\Roaming\DBeaverData\workspace6\General\timezone_posix.sql打开cmd进入mysql.exe所在目录我的是 C:\Program Files\MySQL\MySQL Server 8.4\bin执行命令mysql-uroot-pmysqlC:\Users\v-liuyuhang\AppData\Roaming\DBeaverData\workspace6\General\timezone_posix.sql4.3.2 Linux系统大多数 Linux 发行版和 macOS 在 /usr/share/zoneinfo 下都有 IANA 数据库直接用一行管道加载mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql-uroot-pmysql4.3.4 云数据库mysql--hostxxx.com--port3306--userxxx--password--ssl-modeREQUIRED mysqlC:\Users\v-liuyuhang\AppData\Roaming\DBeaverData\workspace6\General\timezone_posix.sql然后弹出 Enter password: 输入密码4.3.3 验证SELECTNOW()ASmysql_original_time,CONVERT_TZ(NOW(),00:00,America/Los_Angeles)ASlos_angeles_time,CONVERT_TZ(NOW(),00:00,08:00)ASbeijing_time;返回mysql_original_timelos_angeles_timebeijing_time2026-07-31 17:36:022026-07-31 10:36:022026-08-01 01:36:02 【工作杂谈】20260731_Mysql的时区转换
文章目录1、问题引入2、时间解析2.1 什么是 UTC 时间2.1.1 UTC 是全球统一的时间基准2.1.2 UTC 和 GMT 的关系2.2 什么是北京时间CST2.2.1 北京时间没有夏令时切换2.3 什么是 PST 和 PDT2.3.1 PSTPacific Standard Time2.3.2 PDTPacific Daylight Time2.3.3 PST 与 PDT 的关系2.4 PST/PDT 与北京时间相差多少3、跨时区项目一般怎么处理原则一后端和数据层统一使用 UTC原则二展示时才转成用户当地时间4、我的项目怎么处理4.1 查看MySQL Server 和 Session 当前使用的时区4.2 查看命名时区表是否已经加载4.3 开启时区数据库4.3.1 windows系统4.3.2 Linux系统4.3.4 云数据库4.3.3 验证我的网站原文 https://eleanora-lyh.github.io/MyLearningNotes/csdn处的文章会尽快同步更新欢迎大家来访问1、问题引入先介绍一下上游数据L1事件明细记录了Web 侧事件级遥测数据包含用户行为及部分系统/测试/机器人事件。此文件每一个小时记录会生成一个每个大小为5-30TB左右。假设当前时间为curr_time我们需要得到curr_time-2h, curr_time-1h, curr_time, curr_time1h共计四个文件才能开始当前时间为curr_time的计算任务。每两个小时开始一个这样的计算任务也就是说一天需要完成12个并生成对应12个文件每个计算任务完成都会在数据库插入一条记录。目前的保障措施因为上游数据数据达到的时间不确定所以如果想保证计算任务顺利执行在设计ETL流程时会根据当前时间预判可能的到达时间并wait10h在此期间如果需要的所有上游文件都到达就会触发计算任务。同时增加了对上游数据的监控在规定时间范围内未到达就会触发通知。但这样做还是有一个遗漏之处就是是上游文件在规定时间范围内到达但是由于计算任务时间过长或者计算量太大就会导致计算任务无法准时完成。而目前监控计算任务是否完成只能通过肉眼去查文件是否生成。因此引入我的任务在grafana中新建一个dashboard来可视化计算任务的结果用来判断计算任务是否完成实现过程也很简单去查对应时间的数据库内是否有计算任务完成的记录构造本应在一天中生成的12个文件行将其与数据库进行Join对的上的标记为存在对不上的标记为不存在。在实现的过程中我发现自己踩了时区转化的坑导致dashboard中展示的计算结果的create_time和实际create_time不同具体踩坑点如下grafana的dashboard中有时间范围筛选其默认时区为北京时间Mysql数据库的时区为UTC时间计算任务生成的结果存储在Azure云上其时区为PDT时间这样就导致了同一条记录在不同的地方查询显示/操作的create_time都是不同的所以此文章介绍一下时区转换2、时间解析2.1 什么是 UTC 时间2.1.1 UTC 是全球统一的时间基准UTC 全称Coordinated Universal Time协调世界时它不是某个国家或城市的地方时间而是全球系统进行时间同步时使用的基准。常见表示方式2026-07-31T06:30:00Z2026-07-31日期T日期和时间的分隔符06:30:00时、分、秒Z表示 UTC也叫 Zulu Time2.1.2 UTC 和 GMT 的关系日常业务中UTC0和GMT0两者通常可以视为相同偏移但概念上并不完全相同UTCCoordinated Universal Time现代时间标准基于原子钟等时间体系。GMT Greenwich Mean Time格林尼治平均时最初基于天文观测也常被用作时区名称。对于日志、数据库、API、Pipeline 和告警系统建议统一使用UTC而不是 GMT。2.2 什么是北京时间CST北京时间CST通常指China Standard TimeCST常表示为 UTC08:00换算关系UTC 2026-07-31 16:30对应北京时间 2026-08-01 00:30注意减去或加上 8 小时后日期也可能变化2.2.1 北京时间没有夏令时切换目前中国标准时间全年固定为UTC8因此在当前规则下夏天是 UTC8冬天也是 UTC8不存在北京时间突然跳过一小时或重复一小时的问题不过跨时区项目中不要把它简单写成模糊的CST。因为CST可能被解释为China Standard TimeCentral Standard TimeCuba Standard Time更明确的写法是UTC08:00和Asia/Shanghai2.3 什么是 PST 和 PDTPST 和 PDT 都与北美太平洋时区有关他们是北美太平洋时区在不同月份范围的细分时区但是可以统称为Pacific Time缩写为PT即PT PST 或 PDT按美国现行的一般规则夏令时从每年 3 月第二个星期日开始到 11 月第一个星期日结束。这个时间范围内采用PDT时区剩余时间范围采用PST时区。2.3.1 PSTPacific Standard Time太平洋标准时间 Pacific Standard Time 缩写PST主要对应不使用夏令时(DST)的时期UTC 偏移UTC-8比 UTC 慢 8 小时使用地区美国西海岸加州、华盛顿州、俄勒冈州、内华达州等、加拿大不列颠哥伦比亚省主要城市包括洛杉矶、旧金山、西雅图、波特兰、温哥华启用时段每年11 月第一个星期日到次年3 月第二个星期日即冬令时夏令时切换3 月第二个星期日起切换为PDTUTC-711 月第一个星期日回切到 PST2.3.2 PDTPacific Daylight Time太平洋夏令时间 Pacific Daylight TimePDT 在实行夏令时期间使用UTC 偏移PDT UTC-7比 UTC 晚 7 小时使用地区美国西海岸加州、华盛顿州、内华达州、俄勒冈州等、加拿大不列颠哥伦比亚省主要城市包括洛杉矶、旧金山、西雅图、波特兰、温哥华等启用时段每年3 月第二个星期日到11 月第一个星期日冬令时切换夏令时结束后切换回PSTPacific Standard Time太平洋标准时间UTC-8 注意PDT 只在夏令时期间有效3 月中11 月初。其余月份美国西海岸用的是 PSTUTC-8。如果你的业务时间跨度覆盖全年不能简单地固定减 7 小时应该用命名时区让 MySQL 自动处理切换CONVERT_TZ(utc_time,00:00,America/Los_Angeles)2.3.3 PST 与 PDT 的关系两者指的是同一个地理区域的同一条时间线只是根据季节在标准时间和夏令时之间切换时段缩写全称UTC 偏移冬季11 月3 月PSTPacificStandard TimeUTC-8夏季3 月11 月PDTPacificDaylight TimeUTC-7也就是说PDT PST 1 小时。夏令时开始时时钟拨快 1 小时PST → PDT结束时拨慢 1 小时PDT → PST。 所以严格来说Pacific TimePT是一个统称它底下分 PST 和 PDT 两种状态随季节切换。2.4 PST/PDT 与北京时间相差多少已知PDT UTC-7PST UTC-8北京时间 UTC8所以可以得出太平洋当地时间UTC 偏移与北京时间的时差PSTUTC-8CSTPST16HPDTUTC-7CSTPST15H3、跨时区项目一般怎么处理原则一后端和数据层统一使用 UTC建议以下内容全部保存为 UTC数据库时间字段Pipeline 的运行时间文件到达时间日志时间API 请求时间事件发生时间告警检测时间消息队列中的时间分布式系统 Watermark审计记录例如2026-07-31T06:30:00Z而不是只保存2026-07-31 14:30:00后者没有说明时区无法判断它是北京时间、UTC 还是美国当地时间。原则二展示时才转成用户当地时间典型流程是事件发生 ↓ 转换为 UTC ↓ 数据库按 UTC 保存 ↓ API 返回 UTC ↓ 前端根据用户时区显示4、我的项目怎么处理因为想的是替换掉手动去云存储那里一个个查文件的过程所以dashboard这里显示的时间要和云存储的create_time一致MySQL create_time UTC 云存储 create_time 洛杉矶当地时间针对目标查询表加一层view视图将转为洛杉矶当地时间如下CONVERT_TZ(create_time,00:00,America/Los_Angeles)如果转换完得到的时间为NULL是因为MySQL 必须查询内部时区表以确定America/Los_Angeles是否存在2026 年 7 月 31 日是否处于夏令时此时应该使用 UTC-7 还是 UTC-8夏令时切换边界如何处理当前 MySQL 查不到对应的时区规则因此返回NULL。解决办法详见下面的4.1、4.2、4.34.1 查看MySQL Server 和 Session 当前使用的时区SELECTsystem_time_zoneASsystem_time_zone,global.time_zoneASglobal_time_zone,session.time_zoneASsession_time_zone;返回system_time_zoneglobal_time_zonesession_time_zoneChina Standard TimeSYSTEMSYSTEM4.2 查看命名时区表是否已经加载SELECTCOUNT(*)AStimezone_countFROMmysql.time_zone_nameWHERENameAmerica/Los_Angeles;返回timezone_count04.3 开启时区数据库要在 MySQL 中使用命名时区如America/Los_Angeles、Asia/Shanghai必须先把系统的IANA 时区数据库zoneinfo加载进 MySQL 自带的mysql.time_zone_*系统表里。否则CONVERT_TZ用命名时区时会返回NULL。4.3.1 windows系统windows系统则首先下载 MySQL 时区包MySQL :: Time zone description tables选择timezone_2026c_posix_sql.zip解压到对应位置我的位置是C:\Users\v-liuyuhang\AppData\Roaming\DBeaverData\workspace6\General\timezone_posix.sql打开cmd进入mysql.exe所在目录我的是 C:\Program Files\MySQL\MySQL Server 8.4\bin执行命令mysql-uroot-pmysqlC:\Users\v-liuyuhang\AppData\Roaming\DBeaverData\workspace6\General\timezone_posix.sql4.3.2 Linux系统大多数 Linux 发行版和 macOS 在 /usr/share/zoneinfo 下都有 IANA 数据库直接用一行管道加载mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql-uroot-pmysql4.3.4 云数据库mysql--hostxxx.com--port3306--userxxx--password--ssl-modeREQUIRED mysqlC:\Users\v-liuyuhang\AppData\Roaming\DBeaverData\workspace6\General\timezone_posix.sql然后弹出 Enter password: 输入密码4.3.3 验证SELECTNOW()ASmysql_original_time,CONVERT_TZ(NOW(),00:00,America/Los_Angeles)ASlos_angeles_time,CONVERT_TZ(NOW(),00:00,08:00)ASbeijing_time;返回mysql_original_timelos_angeles_timebeijing_time2026-07-31 17:36:022026-07-31 10:36:022026-08-01 01:36:02