文章目录
- 获取当前日期、时间
- 日期与时间戳的转换
-
- 获取月份、星期、星期数、天数等函数
- EXTRACT(unit FROM date)
- 时间和秒钟的转换
-
- 计算日期、时间的函数
- DATE_ADD(date,INTERVAL expr unit)
- ADDTIME(expr1,expr2)
- SUBTIME(expr1,expr2)
- DATEDIFF(expr1,expr2)
- TIMEDIFF(expr1,expr2)
- TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
- FROM_DAYS(N)
- TO_DAYS(date)
- LAST_DAY(date)
- MAKEDATE(year,dayofyear)
- MAKETIME(hour,minute,second)
- PERIOD_ADD(P,N)
- 日期的格式化与解析
-
获取当前日期、时间
SELECT NOW() FROM DUAL;
SELECT CURDATE() FROM DUAL;
SELECT CURRENT_DATE() FROM DUAL;
SELECT CURRENT_TIME() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
SELECT CURTIME() FROM DUAL;
SELECT SYSDATE() FROM DUAL;
SELECT UTC_DATE() FROM DUAL;
SELECT UTC_TIME() FROM DUAL;
日期与时间戳的转换
UNIX_TIMESTAMP()
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP("2023-02-28 10:58:10") FROM DUAL;
FROM_UNIXTIME()
SELECT FROM_UNIXTIME(1677679090) FROM DUAL;
获取月份、星期、星期数、天数等函数
SELECT
YEAR (NOW()),
MONTH (CURRENT_TIMESTAMP()),
DAY (NOW()),
HOUR (NOW()),
MINUTE (NOW()),
SECOND (SYSDATE()),
QUARTER (SYSDATE()),
WEEK (NOW()),
WEEKDAY(NOW()),
MONTHNAME('2023-03-03'),
DAYNAME('2023-03-03'),
DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),
DAYOFWEEK(NOW()),
YEARWEEK(NOW())
FROM
DUAL;
EXTRACT(unit FROM date)
SELECT
EXTRACT(YEAR FROM NOW()),
EXTRACT(MONTH FROM SYSDATE()),
EXTRACT(DAY FROM CURRENT_TIMESTAMP()),
EXTRACT(HOUR FROM NOW()),
EXTRACT(MINUTE FROM SYSDATE()),
EXTRACT(SECOND FROM SYSDATE()),
EXTRACT(QUARTER FROM NOW()),
EXTRACT(
WEEK
FROM
CURRENT_TIMESTAMP ()
)
时间和秒钟的转换
TIME_TO_SEC()
SELECT TIME_TO_SEC(CURTIME());
SELECT TIME_TO_SEC(CURRENT_TIME());
SEC_TO_TIME()
SELECT SEC_TO_TIME(81405);
计算日期、时间的函数
DATE_ADD(date,INTERVAL expr unit)
SELECT
DATE_ADD(NOW(), INTERVAL 1 DAY),
DATE_ADD(NOW(), INTERVAL - 1 YEAR),
DATE_ADD(
NOW(),
INTERVAL '1_1' YEAR_MONTH
),
DATE_ADD(
NOW(),
INTERVAL '1' YEAR_MONTH
)
ADDTIME(expr1,expr2)
SELECT ADDTIME('2023-03-03 22:53:34',10);
SELECT ADDTIME('2023-03-03 22:53:34','2:7:30');
SELECT ADDTIME('2023-03-03 22:53:34','33 2:5:30');
SUBTIME(expr1,expr2)
SELECT SUBTIME('2023-03-03 22:53:34',30);
SELECT SUBTIME('2023-03-03 00:53:34','1:1:3');
SELECT SUBTIME('2023-03-03 00:53:34','2 1:1:3');
DATEDIFF(expr1,expr2)
SELECT DATEDIFF('2023-03-03','2022-12-07');
TIMEDIFF(expr1,expr2)
- 返回两个时间(expr1减去expr2)相减得到的差值,格式为hh:mm:ss
SELECT TIMEDIFF('2023-03-07 22:56:56','2023-03-06 08:32:09');
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
- 返回(datetime_expr2-datetime_expr1)的时间差,结果单位由unit参数给出。
SELECT TIMESTAMPDIFF(HOUR,'2023-03-07 22:56:56','2023-03-06 08:32:09');
SELECT TIMESTAMPDIFF(DAY,'2023-03-07 22:56:56','2023-03-10 08:32:09');
FROM_DAYS(N)
TO_DAYS(date)
SELECT FROM_DAYS(738946),TO_DAYS('2023-03-02') FROM DUAL;
LAST_DAY(date)
SELECT LAST_DAY(NOW());
MAKEDATE(year,dayofyear)
SELECT MAKEDATE(YEAR(NOW()),96),MAKEDATE(2023,96);
MAKETIME(hour,minute,second)
SELECT MAKETIME(12,34,36);
PERIOD_ADD(P,N)
- 在格式 YYMM 或 YYYYMM 中添加给定期间内的给定月份数
SELECT PERIOD_ADD(2302,5),PERIOD_ADD(202302,5),PERIOD_ADD(2023-03,-10);
日期的格式化与解析
日期格式化
SELECT employee_id,DATE_FORMAT(hire_date,'%Y-%M-%D') FROM employees ;
SELECT employee_id,DATE_FORMAT(hire_date,'%Y-%m-%d') FROM employees ;
SELECT employee_id,DATE_FORMAT(hire_date,'%Y-%M-%D %h:%i:%S %W %w %T %r') FROM employees ;
日期解析
SELECT STR_TO_DATE('2023-March-1st 10:19:49 Wednesday 3','%Y-%M-%D %h:%i:%S %W %w') FROM DUAL;