Oracle如何在SQL语句中对时间操作、运算

目录

• 0、date与timestamp
• 1、获取系统当前时间
• 2、ORACLE里获取一个时间的年、季、月、周、日的函数:
• 3、日期操作
• 4、常用的时间戳
• 5、查询某时间范围
• 总结

TO_DATE(to_char(xxxTimestamp, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')

TO_TIMESTAMP(to_char(xxxDate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')

select to_char(sysdate, ‘yyyy' ) from dual; --年
select to_char(sysdate, ‘MM' ) from dual; --月
select to_char(sysdate, ‘dd' ) from dual; --日
select to_char(sysdate, ‘Q') from dual; --季
select to_char(sysdate, ‘iw') from dual; --周–按日历上的那种，每年有52或者53周

select sysdate - interval ‘7' MINUTE from dual;

select sysdate - interval ‘7' hour from dual;

select sysdate - interval ‘7' day from dual;

select sysdate - interval ‘7' month from dual;

select sysdate - interval ‘7' year from dual;

select sysdate - 8*interval ‘7' hour from dual;

//获取当年的一月一号
to_date(concat((select to_char(sysdate,‘yyyy') from dual), ‘-01-01 00:00:00'),‘yyyy-MM-dd HH24:mi:ss') //date格式
//获取这个月的一月一号
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 FROM DUAL; //date格式

SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1,‘yyyy-mm-dd HH24:mi:ss') FROM DUAL;

SELECT users.* 
FROM   users
WHERE  create_time >= '2021-12-01 00:00:00' 
AND  create_time <= '2021-12-06 00:00:00'

SELECT users.* 
FROM   users
WHERE  create_time 
BETWEEN '2021-12-01' AND '2021-12-07';