南大通用GBase 8a数据库timestampdiff函数计算两个日期相差的年月日星期小时分秒毫秒等

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数据库两个时间相差的秒数

其它日期函数请参考

http://www.gbase8.cn/?tag=%e6%97%a5%e6%9c%9f