常用函数

一、测试环境
名称    值
cpu    12th Gen Intel® Core™ i7-12700H
操作系统    CentOS Linux release 7.9.2009 (Core)
内存    3G
逻辑核数    2
Gbase-8a数据库版本    9.5.3.27
二、函数介绍
1、HEX
（1）说明
将数字或字符串转换成十六进制形式。

（2）例子
gbase> SELECT HEX(15);
+---------+
| HEX(15) |
+---------+
| F       |
+---------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT HEX('SUN');
+------------+
| HEX('SUN') |
+------------+
| 53554E     |
+------------+
1 row in set (Elapsed: 00:00:00.00)

2、ROUND
（1）说明
ROUND(参数1,参数2)

参数1：为需要四舍五入的数字。

参数2：为需要保留的位数。

（2）例子
gbase> SELECT ROUND(123);
+------------+
| ROUND(123) |
+------------+
|        123 |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456);
+-------------------+
| ROUND(123.123456) |
+-------------------+
|               123 |
+-------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456,-4);
+----------------------+
| ROUND(123.123456,-4) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456,-2);
+----------------------+
| ROUND(123.123456,-2) |
+----------------------+
|                  100 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT ROUND(123.123456,4); 
+---------------------+
| ROUND(123.123456,4) |
+---------------------+
|            123.1235 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

3、CEILING
（1）说明
向上取整。

（2）例子
gbase> SELECT CEILING(123.1);
+----------------+
| CEILING(123.1) |
+----------------+
|            124 |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT CEILING(123.12);
+-----------------+
| CEILING(123.12) |
+-----------------+
|             124 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT CEILING(123.9); 
+----------------+
| CEILING(123.9) |
+----------------+
|            124 |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

4、FLOOR
（1）说明
向下取整。

（2）例子
gbase> SELECT FLOOR(123);
+------------+
| FLOOR(123) |
+------------+
|        123 |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT FLOOR(123.1);
+--------------+
| FLOOR(123.1) |
+--------------+
|          123 |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT FLOOR(123.9);
+--------------+
| FLOOR(123.9) |
+--------------+
|          123 |
+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT FLOOR(123.92);
+---------------+
| FLOOR(123.92) |
+---------------+
|           123 |
+---------------+
1 row in set (Elapsed: 00:00:00.00)

5、TRUNCATE
（1）说明
截断字符串的作用。

TRUNCATE(X,Y)

X:为需要截断的数字。

Y:为需要截断的位数，如果为正数，截断小数点之后的Y位，如果为负数，截断小数点前的Y位，不四舍五入。

（2）例子
gbase> SELECT TRUNCATE(123);  
+---------------+
| TRUNCATE(123) |
+---------------+
|           123 |
+---------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123);
+-------------------+
| TRUNCATE(123.123) |
+-------------------+
|               123 |
+-------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123,1);
+---------------------+
| TRUNCATE(123.123,1) |
+---------------------+
|               123.1 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123,-1);
+----------------------+
| TRUNCATE(123.123,-1) |
+----------------------+
|                  120 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT TRUNCATE(123.123,2); 
+---------------------+
| TRUNCATE(123.123,2) |
+---------------------+
|              123.12 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

6、返回当前时间函数
（1）说明
NOW(),SYSDATE(),CURRENT_DATE(),CURDATE(),CURRENT_TIME(),CURTIME()都是返回当前时间函数，但返回的时间类型不尽相同。

SYSDATE和NOW区别：
SYSDATE:返回该函数执行时的时间。
NOW:返回的是语句开始执行的时间，在语句执行结束前不会变化。

（2）例子
gbase> select NOW(), SYSDATE(),CURRENT_DATE(), CURDATE(),CURRENT_TIME(),CURTIME() ;
+---------------------+---------------------+----------------+------------+----------------+-----------+
| NOW()               | SYSDATE()           | CURRENT_DATE() | CURDATE()  | CURRENT_TIME() | CURTIME() |
+---------------------+---------------------+----------------+------------+----------------+-----------+
| 2024-12-15 15:58:32 | 2024-12-15 15:58:32 | 2024-12-15     | 2024-12-15 | 15:58:32       | 15:58:32  |
+---------------------+---------------------+----------------+------------+----------------+-----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT NOW(), SYSDATE(),SLEEP(6),NOW(), SYSDATE();
+---------------------+---------------------+----------+---------------------+---------------------+
| NOW()               | SYSDATE()           | SLEEP(6) | NOW()               | SYSDATE()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2024-12-15 15:59:17 | 2024-12-15 15:59:17 |        0 | 2024-12-15 15:59:17 | 2024-12-15 15:59:23 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (Elapsed: 00:00:06.00)

7、WEEK
（1）说明
返回当前日期是对应年份的第几周。

WEEK(date[,mode])

date：需要转换成周数的日期时间。

mode：0-9

如果不写mode参数，取参数default_week_format的值。

（2）例子
gbase> SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
|          50 |
+-------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2025-01-01');
+--------------------+
| WEEK('2025-01-01') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2025-01-01',5);
+----------------------+
| WEEK('2025-01-01',5) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2025-01-01',9);
+----------------------+
| WEEK('2025-01-01',9) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2025-01-08',9);
+----------------------+
| WEEK('2025-01-08',9) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEK('2025-01-07',9);
+----------------------+
| WEEK('2025-01-07',9) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SHOW VARIABLES LIKE '%default_week_format%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set (Elapsed: 00:00:00.01)

8、WEEKDAY
（1）说明
得到当前日期是星期几。

WEEKDAY(date)

返回0-6，表示星期一到星期日。

（2）例子
gbase> SELECT WEEKDAY('2024-12-16');
+-----------------------+
| WEEKDAY('2024-12-16') |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEKDAY('2024-12-10');
+-----------------------+
| WEEKDAY('2024-12-10') |
+-----------------------+
|                     5 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)

9、WEEKOFYEAR
（1）说明
返回DATE是对应年份的第几周。

WEEKOFYEAR(date)

WEEKOFYEAR(date)等价于 WEEK(date,3)。

（2）例子
gbase> SELECT WEEKOFYEAR('2024-12-16');
+--------------------------+
| WEEKOFYEAR('2024-12-16') |
+--------------------------+
|                       50 |
+--------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEKOFYEAR('2024-01-01');
+--------------------------+
| WEEKOFYEAR('2024-01-01') |
+--------------------------+
|                       52 |
+--------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT WEEKOFYEAR('2024-01-3');
+-------------------------+
| WEEKOFYEAR('2024-01-3') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (Elapsed: 00:00:00.00)

10、LAST_DAY
（1）说明
查询日期月份最后一天；

错误日期返回NULL；

（2）例子
gbase> SELECT LAST_DAY(NOW());        
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2024-12-31      |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT LAST_DAY('2024-01-01');
+------------------------+
| LAST_DAY('2024-01-01') |
+------------------------+
| 2022-01-31             |
+------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> SELECT LAST_DAY('2024-01-32');
+------------------------+
| LAST_DAY('2024-01-32') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)

gbase> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2024-01-32' |
+---------+------+----------------------------------------+
1 row in set (Elapsed: 00:00:00.00)