老干支部网站建设方案,怎样做医院网站,郑州网站高端网站设计,兴义哪有做网站带您了解TiDB MySQL数据库中关于日期、时间的坑时间的基础知识什么是时间计算时间的几种方法世界时#xff08;UT#xff09;协调世界时#xff08;UTC#xff09;国际原子时#xff08;TAI#xff09;时区的概念中国所在的时区操作系统的时区datetimedatectl数据库…
带您了解TiDB MySQL数据库中关于日期、时间的坑时间的基础知识什么是时间计算时间的几种方法世界时UT协调世界时UTC国际原子时TAI时区的概念中国所在的时区操作系统的时区datetimedatectl数据库中的时区TiDB数据库中的时区查看时区参数与时间相关的函数与时间相关的数据类型与日期相关的函数如何计算闰年MySQL数据库中的时区日期相关的函数MySQL数据库中的定时器总结时间的基础知识
什么是时间
时间是人们根据自然现象而进行的一种抽象概念。人类通过观察日月星辰、季节交替、动植物生长等自然现象逐渐认识到时间的存在并进行了一系列的时间计量。
计算时间的几种方法
世界时UT
世界时Universal Time简称UT是以地球自转周期作为基础的时间计量标准UT可以根据不同的定义方式分为UT0、UT1、UT2、UT1R等不同的形式。UT的定义是基于天文观测数据主要通过观测地球自转的角速度来确定时间单位。UT是地球物理学中使用最广泛的时间系统之一但它受到地球自转速率的不断变化的影响因此不够稳定和准确。
为了弥补UT的不足国际标准化组织ISO于1970年提出了协调世界时UTC。
协调世界时UTC
协调世界时Coordinated Universal Time简称UTC又称世界统一时间世界标准时间是目前国际上通用的时间标准它是以原子钟为基础通过对全球范围内的原子钟信号进行加权平均而得出的一种时间标准。
协调世界时采用24小时制一天被划分为24个小时每小时包括60分钟每分钟包括60秒。起点是1970年1月1日0时0分0秒与Unix时间戳相同。
协调世界时以地球自转为基础以本初子午线上的标准子午线时为基准使地球各地的时间保持一致。UTC引入了闰秒来纠正由于地球自转速率的微小变化所导致的时间偏差。
国际原子时TAI
国际原子时International Atomic Time简称TAI是一个标准的原子时计量系统以铯原子的振荡频率为基础是世界上精度最高的时间计量方式之一。TAI通过一个网络由多个原子钟测量得出并被国际计量组织BIPM所管理。
国际原子时与世界时UT之间存在微小的偏差这是由于地球自转速度的变化导致的。因此国际地球自转服务(IERS)会不定期地宣布添加或减少闰秒以保持TAI和UT之间的同步。
时区的概念
时区是按照一定的经度范围将地球划分成若干个区域使得同一个时区内的时间是相同的以方便人们进行时间交流和统一管理。世界上总共分为24个主要时区每个主要时区相差15度经度。
通常时区以协调世界时UTC的正负偏移量来表示偏移量为0的时区被称为“格林威治标准时间”GMT也就是世界标准时间。
全球24个标准时区及其相应的偏差以协调世界时UTC为基准
UTC-12: 国际日期变更线以西12小时UTC-11: 协调世界时减去11小时UTC-10: 协调世界时减去10小时UTC-9: 协调世界时减去9小时UTC-8: 协调世界时减去8小时UTC-7: 协调世界时减去7小时UTC-6: 协调世界时减去6小时UTC-5: 协调世界时减去5小时UTC-4: 协调世界时减去4小时UTC-3: 协调世界时减去3小时UTC-2: 协调世界时减去2小时UTC-1: 协调世界时减去1小时UTC: 协调世界时UTC1: 协调世界时加上1小时UTC2: 协调世界时加上2小时UTC3: 协调世界时加上3小时UTC4: 协调世界时加上4小时UTC5: 协调世界时加上5小时UTC6: 协调世界时加上6小时UTC7: 协调世界时加上7小时UTC8: 协调世界时加上8小时UTC9: 协调世界时加上9小时UTC10: 协调世界时加上10小时UTC11: 协调世界时加上11小时UTC12: 协调世界时加上12小时
中国所在的时区
中国所在的时区是指中国范围内划分的标准时区中国的大致经度范围是73°33′E至135°05′E本应跨越5个时区但为了统一管理、保证全国统一时间中国只采用了一个时区即东八区UTC8。中国除了采用UTC8表示时区还可以使用中国标准时间China Standard TimeCST来表示。
操作系统的时区
在Ubuntu 20.04系统中有几个命令是和时间、时区有关的。
date
可以通过date命令来查看时间在输出结果中同时会显示当前时间是按什么时间计时的。
wux_labswux-labs-vm:~$ date
Wed Feb 22 06:39:29 UTC 2023从输出结果可以看到当前是UTC时间2023-02-22。
timedatectl
还可以使用timedatectl命令查看时间的详细信息。
wux_labswux-labs-vm:~$ timedatectlLocal time: Wed 2023-02-22 06:39:35 UTCUniversal time: Wed 2023-02-22 06:39:35 UTCRTC time: Wed 2023-02-22 06:39:35 Time zone: Etc/UTC (UTC, 0000)
System clock synchronized: yes NTP service: active RTC in local TZ: no从结果中可以看到当前时间的详细信息以及时区信息
Time zone: Etc/UTC (UTC, 0000) 使用timedatectl list-timezones命令可以列出所有可使用的时区。
使用timedatectl set-timezone命令可以修改系统的时区。
以下命令将系统时区修改为Asia/Shanghai
wux_labswux-labs-vm:~$ sudo timedatectl set-timezone Asia/Shanghai修改完成后再次查看一下时间信息
wux_labswux-labs-vm:~$ date
Wed Feb 22 14:39:48 CST 2023
wux_labswux-labs-vm:~$ timedatectlLocal time: Wed 2023-02-22 14:39:52 CSTUniversal time: Wed 2023-02-22 06:39:52 UTCRTC time: Wed 2023-02-22 06:39:52 Time zone: Asia/Shanghai (CST, 0800)
System clock synchronized: yes NTP service: active RTC in local TZ: no数据库中的时区
TiDB数据库中的时区
在TiDB数据库中通过参数time_zone可以设置数据库的时区。TiDB中的参数的作用域分为Global级别和Session级别每个级别都可以设置time_zone参数TiDB在取值的时候优先取Session级别的Session级别没设置再取Global级别的具体的优先级为
Session Global 操作系统 UTC查看时区参数
在TiDB中可以通过以下语句来查看当前集群中的参数。
mysql select global.time_zone,session.time_zone;
-----------------------------------------
| global.time_zone | session.time_zone |
-----------------------------------------
| SYSTEM | SYSTEM |
-----------------------------------------
1 row in set (0.00 sec)在当前集群中time_zone都未设置所以采用的是操作系统的时区。
与时间相关的函数
在TiDB中有一些与时间有关的函数其函数返回值会受时区的影响。
now()函数用于获取当前时间包括日期和时间curtime()函数用于获取当前时间仅包含时间
mysql select now(),curtime();
--------------------------------
| now() | curtime() |
--------------------------------
| 2023-02-22 07:17:45 | 07:17:45 |
--------------------------------
1 row in set (0.00 sec)由于TiDB数据库集群在启动的时候操作系统采用的时间是UTC时间所以当前启动的TiDB数据库集群也采用的UTC时间。 下面修改一下Session级别的参数。
mysql set session time_zone 8:00;
Query OK, 0 rows affected (0.00 sec)修改完成后再看看当前时区参数以及函数返回值都会发生变化。
mysql select global.time_zone,session.time_zone;
-----------------------------------------
| global.time_zone | session.time_zone |
-----------------------------------------
| SYSTEM | 8:00 |
-----------------------------------------
1 row in set (0.00 sec)mysql select now(),curtime();
--------------------------------
| now() | curtime() |
--------------------------------
| 2023-02-22 15:18:06 | 15:18:06 |
--------------------------------
1 row in set (0.00 sec)与时间相关的数据类型
在TiDB中有一些数据类型也与时间有关。
datetime日期时间类型会同时存储日期和时间timestamp时间戳类型会同时存储日期和时间time时间类型仅会存储时间
下面创建一个表用来看看不同数据类型受时区的影响情况。
mysql create table temp(f1 datetime, f2 timestamp, f3 time);
Query OK, 0 rows affected (0.14 sec)插入数据都取当前时间。
mysql insert into temp values(now(), now(), curtime());
Query OK, 1 row affected (0.01 sec)查看时区及时间。
mysql select global.time_zone,session.time_zone;
-----------------------------------------
| global.time_zone | session.time_zone |
-----------------------------------------
| SYSTEM | SYSTEM |
-----------------------------------------
1 row in set (0.00 sec)mysql select * from temp;
----------------------------------------------------
| f1 | f2 | f3 |
----------------------------------------------------
| 2023-02-22 07:27:13 | 2023-02-22 07:27:13 | 07:27:13 |
----------------------------------------------------
1 row in set (0.00 sec)以上结果是采用UTC时间的值数据是持久化到表中的固定不变的值。 接下来切换一下时区再来看看表中的数据情况。
mysql set session time_zone 8:00;
Query OK, 0 rows affected (0.00 sec)mysql select global.time_zone,session.time_zone;
-----------------------------------------
| global.time_zone | session.time_zone |
-----------------------------------------
| SYSTEM | 8:00 |
-----------------------------------------
1 row in set (0.00 sec)mysql select * from temp;
----------------------------------------------------
| f1 | f2 | f3 |
----------------------------------------------------
| 2023-02-22 07:27:13 | 2023-02-22 15:27:13 | 07:27:13 |
----------------------------------------------------
1 row in set (0.01 sec)从结果可以看到对于已经持久化保存的固定数据
datetime、time两种数据类型的值不受时区影响timestamp数据类型的值会受时区影响时区不同查询结果不同 避坑1 在数据库中使用Timestamp数据类型一定要小心一定要确定好时区分析影响性。 如果可以使用datetime类型而不使用timestamp类型。 与日期相关的函数
在TiDB数据库中有一些关于日期的函数、计算 curdate()用来获取当前时间 date()将字符串转换成日期 day()获取日期的天 interval 1 day日期计算加1天 可用于判断两个日期是否相等相等返回1不等返回0 datediff()用来计算两个日期之间相差的天数
比如下面的代码用来做日期相关的计算
mysql select curdate(), curdate() interval 5 day, day(curdate()), date(2023-02-22), curdate() date(2023-02-22), curdate() date(2023-02-23);
--------------------------------------------------------------------------------------------------------------------------------------------
| curdate() | curdate() interval 5 day | day(curdate()) | date(2023-02-22) | curdate() date(2023-02-22) | curdate() date(2023-02-23) |
--------------------------------------------------------------------------------------------------------------------------------------------
| 2023-02-22 | 2023-02-27 | 22 | 2023-02-22 | 1 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)注意 date()函数仅能将有效的日期字符串转换成日期如果日期不合法将会返回NULL。 在下面的例子中data将正确的日期进行了转换错误的日期返回了NULL。
mysql select date(2023-02-22),date(20230222),date(2023-02-29),date(20230229);
----------------------------------------------------------------------------
| date(2023-02-22) | date(20230222) | date(2023-02-29) | date(20230229) |
----------------------------------------------------------------------------
| 2023-02-22 | 2023-02-22 | NULL | NULL |
----------------------------------------------------------------------------
1 row in set, 2 warnings (0.00 sec)避坑2 不要以为用date()函数可以成功转换的日期都是正常的日期有能够成功转换的也是错误日期。 比如你见过日为00的日期吗 在下面的案例中2023-03-00’是一个错误的日期但是经过date()函数转换后并不是NULL而确实是一个日期。但是这个日期并不等于2023-03-01的前一天。
mysql select date(2023-03-00),date(2023-03-01),date(2023-03-01) - interval 1 day, date(2023-03-00) date(2023-03-01) - interval 1 day;
---------------------------------------------------------------------------------------------------------------------------------------
| date(2023-03-00) | date(2023-03-01) | date(2023-03-01) - interval 1 day | date(2023-03-00) date(2023-03-01) - interval 1 day |
---------------------------------------------------------------------------------------------------------------------------------------
| 2023-03-00 | 2023-03-01 | 2023-02-28 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)这个日期的天是0并且它不是2023-03-01的前一天也不是2023-02-28的后一天。
mysql select day(date(2023-03-00)),day(date(2023-03-01)),date(2023-03-01) - interval 1 day, date(2023-03-00) date(2023-02-28) interval 1 day;
-------------------------------------------------------------------------------------------------------------------------------------------------
| day(date(2023-03-00)) | day(date(2023-03-01)) | date(2023-03-01) - interval 1 day | date(2023-03-00) date(2023-02-28) interval 1 day |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 1 | 2023-02-28 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)由于它不是一个正常的日期所以它无法计算日期之间的差值。
mysql select datediff(date(2023-02-28),date(1970-01-01)),datediff(date(2023-03-00),date(1970-01-01)),datediff(date(2023-03-01),date(1970-01-01));
---------------------------------------------------------------------------------------------------------------------------------------------------
| datediff(date(2023-02-28),date(1970-01-01)) | datediff(date(2023-03-00),date(1970-01-01)) | datediff(date(2023-03-01),date(1970-01-01)) |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 19416 | NULL | 19417 |
---------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)如何计算闰年
在TiDB数据库中可以通过日期的计算函数来计算当年是否是闰年当2月的最后一天是29则是闰年28则不是闰年。
mysql select last_day(date(2023-02-01)), case when day(last_day(date(2023-02-01))) 29 then 是闰年 else 不是闰年 end;
-------------------------------------------------------------------------------------------------------------------------
| last_day(date(2023-02-01)) | case when day(last_day(date(2023-02-01))) 29 then 是闰年 else 不是闰年 end |
-------------------------------------------------------------------------------------------------------------------------
| 2023-02-28 | 不是闰年 |
-------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql select last_day(date(2024-02-01)), case when day(last_day(date(2024-02-01))) 29 then 是闰年 else 不是闰年 end;
-------------------------------------------------------------------------------------------------------------------------
| last_day(date(2024-02-01)) | case when day(last_day(date(2024-02-01))) 29 then 是闰年 else 不是闰年 end |
-------------------------------------------------------------------------------------------------------------------------
| 2024-02-29 | 是闰年 |
-------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)MySQL数据库中的时区 MySQL数据库中的时区与TiDB数据库中的时区一致这里就不重复演示了。 还是需要注意Timestamp数据类型的使用。 日期相关的函数 在MySQL中也有与TiDB相同的关于日期的函数这里也就不重复演示了。 但是有一个不太一样的就是date()TiDB中会将date(‘2023-03-00’)成功转换成日期虽然该日期非法但不为NULL。而在MySQL中date(‘2023-03-00’)是非法的返回NULL。
mysql select date(2023-03-00),date(2023-03-01),date(2023-03-01) - interval 1 day, date(2023-03-00) date(2023-03-01) - interval 1 day;
---------------------------------------------------------------------------------------------------------------------------------------
| date(2023-03-00) | date(2023-03-01) | date(2023-03-01) - interval 1 day | date(2023-03-00) date(2023-03-01) - interval 1 day |
---------------------------------------------------------------------------------------------------------------------------------------
| NULL | 2023-03-01 | 2023-02-28 | NULL |
---------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 2 warnings (0.00 sec)MySQL数据库中的定时器 在TiDB数据库中不支持存储过程、自定义函数、触发器、定时器等。 在MySQL数据库中可以创建定时器。
下面的语句创建一个定时器每10秒钟往表中插入一条记录5分钟后结束定时器功能。
mysql create event insert_event- on schedule every 10 second- ends now() interval 5 minute- do insert into temp values(now(), now(), curtime());
Query OK, 0 rows affected (0.02 sec)定时器创建完成后每隔一段时间查看数据库表的数据可以看到数据的增长。 总结
好了以上就是数据库中关于日期、时间的使用在TiDB中有几个坑、MySQL中也有几个坑你遇到过吗使用的时候一定要注意。
最后我们实现了在数据库中判断闰年、定时器等功能。
