extract()

date中获取年、月、日

select  extract (year from sysdate) year, extract (month from sysdate) month, extract (timezone_hour from sysdate) day from  dual;

select extract (year from date '2022-09-15') year, extract (month from date'2022-09-15') month, extract (day from date '2022-09-15') day from dual;

从timestamp中获取年、月、日、时、分、秒

SELECT
	systimestamp,extract( year FROM systimestamp ) year,
	extract( month FROM systimestamp ) month,
	extract( day FROM systimestamp ) day,
	extract( minute FROM systimestamp ) minute,
	extract( second FROM systimestamp ) second,
	extract( timezone_hour FROM systimestamp ) th,
	extract( timezone_minute FROM systimestamp ) tm,
	extract( timezone_region FROM systimestamp ) tr,
	extract( timezone_abbr FROM systimestamp ) ta 
FROM
	dual

计算时间差

SELECT
time2-time1 time,
	extract( day FROM time2 - time1 ) day,
	extract( hour FROM time2 - time1 ) hour,
	extract( minute FROM time2 - time1 ) minute,
	extract( second FROM time2 - time1 ) second 
FROM
	( SELECT to_timestamp( '2011-8-1 20:28:41', 'yyyy-mm-dd hh24:mi:ss' ) time1, to_timestamp( '2022-01-08 8:04:26', 'yyyy-mm-dd hh24:mi:ss' ) time2 FROM dual )