GBase 8a数据库支持计算2个日期的差距,通过timestampdiff函数,得到指定格式的差距结果,包括年份差year,月份差month,星期差week,天数差day,小时差hour,分钟差minute,秒数差second,毫秒差microsecond等常见的内置数据类型。
目录导航
相差天时分秒的完整样例
gbase> set @d1='2021-05-02';set @d2='2021-01-01 12:23:34';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select timestampdiff(day,@d2,@d1) day,timestampdiff(hour,@d2,@d1)%24 hour,timestampdiff(minute,@d2,@d1)%60 min,timestampdiff(second,@d2,@d1)%60 second;
+------+------+------+--------+
| day | hour | min | second |
+------+------+------+--------+
| 120 | 11 | 36 | 26 |
+------+------+------+--------+
1 row in set (Elapsed: 00:00:00.00)
2个日期相差天数
gbase> select now(),timestampdiff(day,'2020-01-01',now());
+---------------------+---------------------------------------+
| now() | timestampdiff(day,'2020-01-01',now()) |
+---------------------+---------------------------------------+
| 2020-09-01 12:28:44 | 244 |
+---------------------+---------------------------------------+
1 row in set (Elapsed: 00:00:00.10)
2个日期相差星期数量
gbase> select now(),timestampdiff(week,'2020-01-01',now());
+---------------------+----------------------------------------+
| now() | timestampdiff(week,'2020-01-01',now()) |
+---------------------+----------------------------------------+
| 2020-09-01 12:28:52 | 34 |
+---------------------+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
2个日期相差月数量
gbase> select now(),timestampdiff(month,'2020-01-01',now());
+---------------------+-----------------------------------------+
| now() | timestampdiff(month,'2020-01-01',now()) |
+---------------------+-----------------------------------------+
| 2020-09-01 12:28:57 | 8 |
+---------------------+-----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
2个日期相差年数量
gbase> select now(),timestampdiff(year,'2020-01-01',now());
+---------------------+----------------------------------------+
| now() | timestampdiff(year,'2020-01-01',now()) |
+---------------------+----------------------------------------+
| 2020-09-01 12:29:03 | 0 |
+---------------------+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
2个日期相差小时数量
gbase> select now(),timestampdiff(hour,'2020-01-01',now());
+---------------------+----------------------------------------+
| now() | timestampdiff(hour,'2020-01-01',now()) |
+---------------------+----------------------------------------+
| 2020-09-01 12:29:32 | 5868 |
+---------------------+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
2个日期相差分钟数量
gbase> select now(),timestampdiff(minute,'2020-01-01',now()); +---------------------+------------------------------------------+ | now() | timestampdiff(minute,'2020-01-01',now()) | +---------------------+------------------------------------------+ | 2020-09-01 12:29:47 | 352109 | +---------------------+------------------------------------------+ 1 row in set (Elapsed: 00:00:00.00)
2个日期相差秒数
gbase> select now(),timestampdiff(second,'2020-01-01',now()); +---------------------+------------------------------------------+ | now() | timestampdiff(second,'2020-01-01',now()) | +---------------------+------------------------------------------+ | 2020-09-01 12:29:53 | 21126593 | +---------------------+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
2个日期相差微秒数
gbase> select now(),timestampdiff(frac_second,'2020-01-01',now());
+---------------------+-----------------------------------------------+
| now() | timestampdiff(frac_second,'2020-01-01',now()) |
+---------------------+-----------------------------------------------+
| 2020-09-01 12:30:02 | 21126602000000 |
+---------------------+-----------------------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)
gbase> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------+
| Warning | 1287 | The syntax 'FRAC_SECOND' is deprecated and will be removed in GBase 6.2. Please use MICROSECOND instead |
+---------+------+---------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select now(),timestampdiff(microsecond,'2020-01-01',now());
+---------------------+-----------------------------------------------+
| now() | timestampdiff(microsecond,'2020-01-01',now()) |
+---------------------+-----------------------------------------------+
| 2020-09-01 12:30:25 | 21126625000000 |
+---------------------+-----------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
参考
其它日期函数请参考
《南大通用GBase 8a数据库timestampdiff函数计算两个日期相差的年月日星期小时分秒毫秒等》有1条评论
评论已关闭。