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.
Oracle and MySQL functions
MySQL functions related to date calculation include CURDATE/CURRENT_DATE, CURTIME/CURRENT_TIME, and CURRENT_TIMESTAMP/NOW.
Oracle functions related to date calculation include 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 table t1. 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 table t1. ODP can accurately route the SQL query.
select * from t1 where c1=TO_DATE('20230823024916','YYYYMMDDHH24MISS');
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 table t1. ODP can accurately route the SQL query.
select * from t1 where c1=to_timestamp('20230823191416','YYYY-MM-DD HH24:MI:SSXFF');