This topic describes how to use date and time functions in queries and provides examples.
Time functions
In MySQL tenant mode, frequently-used date and time functions include DATE, TIMESTAMP, TIME, DATETIME, and YEAR. In MySQL mode of OceanBase Database, frequently-used date and time functions include NOW(), CURDATE(), and CURTIME().
Examples
Adjust the time display format
In MySQL mode of OceanBase Database, you can use the NOW() function to query the current time, and the DATE_FORMAT function to adjust the time display format.
obclient> SELECT NOW(),DATE_FORMAT(NOW(),"%Y/%M/%D %T") new_time;
+---------------------+-------------------------+
| NOW() | new_time |
+---------------------+-------------------------+
| 2022-03-08 18:26:31 | 2022/March/8th 18:26:31 |
+---------------------+-------------------------+
1 row in set
Extract year, month, date, hour, minute, and second values from date and time
In MySQL mode of OceanBase Database, you can use the EXTRACT() function to extract the year, month, date, hour, minute, and second values from date and time.
obclient> SET @dt = NOW();
Query OK, 0 rows affected
obclient> SELECT @dt
, EXTRACT(YEAR FROM @dt) d_year
, EXTRACT(MONTH FROM @dt) d_month
, EXTRACT(week FROM @dt) d_week
, EXTRACT(DAY FROM @dt) d_day
, EXTRACT(HOUR FROM @dt) d_hour
, EXTRACT(MINUTE FROM @dt) d_min
, EXTRACT(SECOND FROM @dt) d_second
, EXTRACT(year_month FROM @dt) d_year_month
, EXTRACT(hour_minute FROM @dt) d_hour_min
\G
*************************** 1. row ***************************
@dt: 2022-03-08 18:28:53
d_year: 2022
d_month: 3
d_week: 10
d_day: 8
d_hour: 18
d_min: 28
d_second: 53
d_year_month: 202203
d_hour_min: 1828
1 row in set
Addition and deduction operations on time
In MySQL mode of OceanBase Database, you can use the DATE_ADD() or DATE_SUB() function to add or subtract date and time. Sample SQL statement:
obclient> SET @dt = NOW();
Query OK, 0 rows affected
obclient> SELECT @dt
, DATE_ADD(@dt, INTERVAL 1 DAY ) t1
, DATE_ADD(@dt, INTERVAL 1 HOUR ) t2
, DATE_ADD(@dt, INTERVAL -10 MINUTE ) t3
, DATE_ADD(@dt, INTERVAL -1 MONTH ) t4
, DATE_ADD(@dt, INTERVAL 1 YEAR ) t5
\G
*************************** 1. row ***************************
@dt: 2022-03-08 18:28:53
t1: 2022-03-09 18:28:53
t2: 2022-03-08 19:28:53
t3: 2022-03-08 18:18:53
t4: 2022-02-08 18:28:53
t5: 2023-03-08 18:28:53
1 row in set
For information about time types, see the SQL syntax (MySQL mode) section.