This topic describes how to use date calculation functions in OceanBase Database Proxy (ODP).
Notice
The current time in a function is calculated based on ODP, which may differ from the calculation result of an OBServer node. In scenarios that involve HASH partitions and current time calculations, ODP cannot support accurate routing.
ODP V4.3.3 and later support routing parsing for partitioning keys in the TIMESTAMP format.
Oracle and MySQL functions
MySQL functions related to date calculation are CURDATE/CURRENT_DATE, CURTIME/CURRENT_TIME, and CURRENT_TIMESTAMP/NOW.
Oracle functions related to date calculation are CURRENT_DATE, CURRENT_TIMESTAMP, SYSTIMESTAMP, and SYSDATE.
Note
Parentheses are not allowed after Oracle functions but are allowed after MySQL functions.
The
CURRENT_TIMESTAMP(precision)syntax is not supported in Oracle mode.
Assume that the database has a table named t1 and its partitioning key is the c1 column. Example: Select rows where c1 equals the current system date in the format of YYYY/MM/DD from the t1 table. ODP can accurately route the SQL query.
select * from t1 where c1=to_char(sysdate,'YYYY/MM/DD');
TO_DATE
The TO_DATE function converts a string to a date or datetime value. Currently, ODP supports the to_date(char, [fmt]) syntax only.
char: the string to be converted, which represents a specific date or datetime.fmt: the target format. This parameter is optional.
Assume that the database has a table named t1 and its partitioning key is the c1 column. Example: Select rows where c1 equals the datetime converted from the 20230823024916 string from the t1 table. ODP can accurately route the SQL query.
select * from t1 where c1=TO_DATE('20230823024916','YYYYMMDDHH24MISS');
TO_DAYS
The TO_DAYS function returns the number of days corresponding to the specified date.
Note
This function returns the number of days from 0000-01-01.
ODP supports partitioning keys that carry the TO_DAYS function, allowing accurate routing of SQL statements containing this function.
Assume that the database has a table named t1 and its partitioning key is the c1 column. Example: Select rows where c1 equals the number of days between 2015-11-04 and 0000-01-01 from the t1 table.
select * from t1 where c1=TO_DAYS('2015-11-04');
TO_TIMESTAMP
The TO_TIMESTAMP function converts a string to a datetime value of the timestamp type. Currently, ODP supports the to_timestamp(str, [fmt]) syntax only.
str: the string to be converted, which represents a specific datetime.fmt: the target format. This parameter is optional.
Assume that the database has a table named t1 and its partitioning key is the c1 column. Example: Select rows where c1 equals the datetime of the timestamp type converted from the 20230823191416 string from the t1 table. ODP can accurately route the SQL query.
select * from t1 where c1=to_timestamp('20230823191416','YYYY-MM-DD HH24:MI:SSXFF');