Date and time data types that are commonly used in MySQL tenants include DATE, TIMESTAMP, TIME, DATETIME, and YEAR. For more information about date and time data types, see OceanBase SQL Reference (MySQL Mode).
Date and time functions commonly used in MySQL tenants include: NOW(), CURDATE(), and CURTIME().
Example: Formatting the date and time display
In a MySQL tenant, you can use the DATE_FORMAT function to adjust the date and time display format. SQL statement:
obclient> select now(), date_format(now(), "%Y/%m/%d %T") new_time ;
+---------------------+---------------------+
| now() | new_time |
+---------------------+---------------------+
| 2020-04-03 15:55:37 | 2020/04/03 15:55:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)
Example: Extracting year, month, day, hour, minute, or second from a datetime value
In a MySQL tenant, you can use the EXTRACT function to extract year, month, day, hour, minute, or second from a datetime value. SQL statement:
obclient> SET @dt = now();
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: 2020-03-27 18:00:52
d_year: 2020
d_month: 3
d_week: 12
d_day: 27
d_hour: 18
d_min: 0
d_second: 52
d_year_month: 202003
d_hour_min: 1800
1 row in set (0.00 sec)
Example: Adding and subtracting time
In a MySQL tenant, you can use the DATE_ADD or DATE_SUB function to add or subtract time. SQL statement:
obclient> SET @dt = now();
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_sub(@dt, INTERVAL 1 YEAR ) t5
\G
*************************** 1. row ***************************
@dt: 2020-03-27 18:03:44
t1: 2020-03-28 18:03:44
t2: 2020-03-27 19:03:44
t3: 2020-03-27 17:53:44
t4: 2020-02-27 18:03:44
t5: 2019-03-27 18:03:44
1 row in set (0.01 sec)