LightDB Oracle模式支持systimestamp(n)

背景

目前，lightdb 在兼容 oracle，目标是 oracle 用户不用修改 sql 代码无缝切换到 lightdb 数据库。oracle中支持systimestamp(n)函数，n的范围为0-9，因此在23.4版本中，新增对systimestamp(n)函数的全面支持。

注：不带参数的systimestamp在之前版本已经实现。

lightdb@oracle=# select systimestamp from dual;
         systimestamp          
-------------------------------
 2023-11-23 09:34:08.352753+08
(1 row)

使用案例及相关说明

函数说明

systimestamp返回数据库所在系统的系统日期，包括秒的小数部分(oracle支持9位小数，即精确到纳秒级，而lightdb目前支持6位小数，即精确到微秒级)和时区。返回类型为TIMESTAMP WITH TIME ZONE。

参数的含义及使用规范

在systimestamp(n)中，n用来指定系统日期中秒数据小数部分的保留位数的最大值，例如n=3，表示返回的系统日期中，秒数据四舍五入最多保留3位小数。请注意标红字体，这是因为PG会自动省略秒小数位末尾的0，如下图所示：

lightdb@oracle=# select systimestamp(3),systimestamp(6);
        systimestamp        |         systimestamp          
----------------------------+-------------------------------
 2023-11-23 10:04:56.395+08 | 2023-11-23 10:04:56.394574+08
(1 row)

lightdb@oracle=# select systimestamp(3),systimestamp(6);
       systimestamp        |         systimestamp          
---------------------------+-------------------------------
 2023-11-23 10:26:09.42+08 | 2023-11-23 10:26:09.420394+08
(1 row)

参数n必须为0-9的整数，不支持浮点类型，不支持表达式，不支持表列名。

lightdb@oracle=# select systimestamp(1.0);
ERROR:  syntax error at or near "1.0"
LINE 1: select systimestamp(1.0);
                            ^
lightdb@oracle=# create table t(a int,b float);
CREATE TABLE
lightdb@oracle=# insert into t values(1,1.0);
INSERT 0 1
lightdb@oracle=# select systimestamp(a) from t;
ERROR:  syntax error at or near "a"
LINE 1: select systimestamp(a) from t;
                            ^
lightdb@oracle=# select systimestamp(b) from t;
ERROR:  syntax error at or near "b"
LINE 1: select systimestamp(b) from t;
     
lightdb@oracle=# select systimestamp(1+2);
ERROR:  syntax error at or near "+"
LINE 1: select systimestamp(1+2);

lightdb@oracle=# select systimestamp(-1);
ERROR:  syntax error at or near "-"
LINE 1: select systimestamp(-1);
                            ^
lightdb@oracle=# select systimestamp(10);
ERROR:  datetime/interval precision is out of range
LINE 1: select systimestamp(10);
                 

当n>6时，函数返回的秒小数部分仍为最多6位小数，这是由于原生PG只支持6位小数所致。 

lightdb@oracle=# select systimestamp(6),systimestamp(7),systimestamp(8),systimestamp(9);
         systimestamp          |         systimestamp          |         systimestamp          |         systimestamp          
-------------------------------+-------------------------------+-------------------------------+-------------------------------
 2023-11-23 10:17:37.857939+08 | 2023-11-23 10:17:37.857939+08 | 2023-11-23 10:17:37.857939+08 | 2023-11-23 10:17:37.857939+08
(1 row)

lightdb@oracle=# select systimestamp(3),systimestamp(7),systimestamp(8),systimestamp(9);
       systimestamp        |         systimestamp          |         systimestamp          |         systimestamp          
---------------------------+-------------------------------+-------------------------------+-------------------------------
 2023-11-23 10:17:50.61+08 | 2023-11-23 10:17:50.609771+08 | 2023-11-23 10:17:50.609771+08 | 2023-11-23 10:17:50.609771+08
(1 row)

支持sql语句级获取最新时间戳

systimestamp和systimestamp(n)都是支持sql语句级别获取当前时间戳的，即在同一个事务中执行多次，获取的时间戳是不同的，每次获取的都是最新的时间戳。

lightdb@oracle=# begin;
BEGIN
lightdb@oracle=*# select systimestamp,systimestamp(3),systimestamp(9);
         systimestamp         |       systimestamp        |         systimestamp         
------------------------------+---------------------------+------------------------------
 2023-11-23 09:46:12.90956+08 | 2023-11-23 09:46:12.91+08 | 2023-11-23 09:46:12.90956+08
(1 row)

lightdb@oracle=*# select systimestamp,systimestamp(3),systimestamp(9);
         systimestamp          |       systimestamp        |         systimestamp          
-------------------------------+---------------------------+-------------------------------
 2023-11-23 09:46:13.860494+08 | 2023-11-23 09:46:13.86+08 | 2023-11-23 09:46:13.860494+08
(1 row)

lightdb@oracle=*# select systimestamp,systimestamp(3),systimestamp(9);
         systimestamp          |        systimestamp        |         systimestamp          
-------------------------------+----------------------------+-------------------------------
 2023-11-23 09:46:15.242484+08 | 2023-11-23 09:46:15.242+08 | 2023-11-23 09:46:15.242484+08
(1 row)

lightdb@oracle=*# end;
COMMIT