This topic provides specific examples on how to use datetime functions in queries.
Datetime functions
In a MySQL tenant, frequently-used datetime types include DATE, TIMESTAMP, TIME, DATETIME, and YEAR. In the MySQL mode of OceanBase Database, frequently-used datetime functions include NOW(), CURDATE(), and CURTIME().
Examples
Adjust the datetime display format
In the MySQL mode of OceanBase Database, you can use the NOW() function to query the current datetime, and the DATE_FORMAT() function to adjust the datetime 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 datetime
In the MySQL mode of OceanBase Database, you can use the EXTRACT() function to extract the year, month, date, hour, minute, and second values from datetime.
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 datetime
In the MySQL mode of OceanBase Database, you can use the DATE_ADD() or DATE_SUB() function to add or subtract datetime. Here is an example:
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 datetime types, see SQL syntax (MySQL mode).