LightDB在2024RP1版本里对TO_TIMESTAMP函数进行了增强,对日期转换格式自动匹配。
- 格式串的分隔符支持任意的非字母数字的可见Ascii字符
- 输入日期和时间没有分隔符而格式串有分隔符的情况下按照格式串长度进行解析
- 格式字符串模板支持FF,精度到小数点后6位
案例如下:
lightdb@lightdb=# set datestyle to iso;
SET
lightdb@lightdb=# select to_timestamp('20231201 170000','yyyy-mm-dd hh24:mi:ss.ff');
to_timestamp
------------------------
2023-12-01 17:00:00+08
(1 row)
lightdb@lightdb=# select to_timestamp('20231201 170000','yyyy/mm/dd hh24miss.ff');
to_timestamp
------------------------
2023-12-01 17:00:00+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00','yyyy-mm-dd hh24:mi:ss');
to_timestamp
------------------------
2024-01-20 10:30:00+08
(1 row)
lightdb@lightdb=# select to_timestamp('2023-02-14 10:11:12.123','yyyy-mm-dd hh:mi:ss.ff');
to_timestamp
----------------------------
2023-02-14 10:11:12.123+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00','yyyy-mm-dd hh24:mi:ss');
to_timestamp
------------------------
2024-01-20 10:30:00+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00.123456','yyyy-mm-dd hh24:mi:ss.us');
to_timestamp
-------------------------------
2024-01-20 10:30:00.123456+08
(1 row)
lightdb@lightdb=# select to_timestamp('20-jan-24','dd-mon-yy');
to_timestamp
------------------------
2024-01-20 00:00:00+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy-mm-dd hh:mi:ss');
to_timestamp
------------------------
2024-03-18 09:34:56+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm/dd hh:mi:ss');
to_timestamp
------------------------
2024-03-18 09:34:56+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm/dd hh/mi/ss');
to_timestamp
------------------------
2024-03-18 09:34:56+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm-dd hh\mi\ss');
to_timestamp
------------------------
2024-03-18 09:34:56+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy:mm:dd hh\mi\ss');
to_timestamp
------------------------
2024-03-18 09:34:56+08
(1 row)
lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy:mm:dd hh@mi@ss');
to_timestamp
------------------------
2024-03-18 09:34:56+08
(1 row)
lightdb@lightdb=# select to_timestamp('2011-09-14 12:52:42.123456789', 'yyyy-mm-dd hh24:mi:ss.ff');
to_timestamp
-------------------------------
2011-09-14 12:52:42.123456+08
(1 row)
详细语法可参考LightDB官网查看:
LightDB: 更快、更稳、更懂金融的分布式关系型数据库