In MySQL tenant mode, frequently-used date and time functions include DATE, TIMESTAMP, TIME, DATETIME, and YEAR. In OceanBase Database, frequently-used date and time functions include NOW(), CURDATE(), and CURTIME().
This topic describes how to use date and time functions in queries and provides examples.
Example 1: Adjust the display format of date and time
In OceanBase Database, you can use the DATE_FORMAT function to adjust the display format of date and time. Sample SQL statement:
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
Example 2: Extract year, month, date, hour, minute, and second from date and time
In OceanBase Database, you can use the EXTRACT() function to extract the year, month, date, hour, minute, and second from date and time. Sample SQL statement:
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
Example 3: Add and subtract date and time
In 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 SQL Syntax.
