This topic provides examples of how to use time functions in queries.
Time functions
Time types commonly used in tenants include DATE, TIMESTAMP, TIME, DATETIME, and YEAR. Common time functions include NOW(), CURDATE(), and CURTIME().
Examples
Format the display of a time type
You can use NOW() to get the current time, and use the DATE_FORMAT function to format the display of a time type.
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, day, hour, minute, and second from a time value
You can use the EXTRACT() function to extract year, month, day, hour, minute, and second information from a time value.
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
Add or subtract time values
You can use the DATE_ADD() or DATE_SUB() function to add or subtract time. An example SQL statement is shown below:
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 more information about time types, see the SQL Syntax (MySQL-compatible mode) section.