南大通用GBase 8a时间转字符串类型的几种方法

GBase 8a数据库集群，可以通过to_char函数，date_format函数，以及cast,等函数从日期转成字符串，本文介绍各个函数的写法和样例。

date_format

如下是dete_format的使用方法，字符串格式完全自定义。

详情可以参考 GBase 8a数据库函数date_format将日期格式化成指定格式的文本

gbase> create table a1 as select date_format(now(),'%Y-%m-%d %H:%i:%s') d from dual;
Query OK, 1 row affected (Elapsed: 00:00:00.26)

gbase> desc a1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| d     | varchar(24) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from a1;
+---------------------+
| d                   |
+---------------------+
| 2021-01-04 14:32:11 |
+---------------------+
1 row in set (Elapsed: 00:00:00.01)

to_char

完全与Oracle语法兼容的写法，自定义格式。

gbase> create table a2 as select to_char(now(),'yyyy-mm-dd') d from dual;
Query OK, 1 row affected (Elapsed: 00:00:00.28)

gbase> select * from a1;
+------------+
| d          |
+------------+
| yyyy-mm-dd |
+------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from a2;
+------------+
| d          |
+------------+
| 2021-01-04 |
+------------+
1 row in set (Elapsed: 00:00:00.01)

cast / convert

本方法只是类型强制转换，其字符串格式取决于默认的日期格式。

有关cast/convert的详细内容，请参考

GBase 8a的强制类型转换cast和convert的用法例子

gbase> create table a3 as select cast(now() as char) d from dual;
Query OK, 1 row affected (Elapsed: 00:00:00.27)

gbase> desc a3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| d     | varchar(19) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from a3;
+---------------------+
| d                   |
+---------------------+
| 2021-01-04 14:38:49 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> show variables like '%datetime%';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
1 row in set (Elapsed: 00:00:00.00)