1.获取当前时间、当前日期、当前时间戳

SELECT CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP

2.天数加减

-- 昨天
SELECT CURRENT_TIMESTAMP - 1

-- 明天
SELECT CURRENT_TIMESTAMP + 1

-- 30日后
SELECT CURRENT_TIMESTAMP + 30

3.月份加减

-- 当前日期1月后
SELECT add_months(CURRENT_DATE, 1)

4.获取指定日期的最小时间

SELECT DATE_TRUNC('day', NOW()) AS today_end;//获取今天的最小时间

5. 获取两个日期时间相差的天数

select
       FLOOR( specialDateParam - sysdate) days,    specialDateParam - sysdate  remainDays        
        FROM  your_table where conditon;

6. 获取 本周、本月、本年第一天。

WITH dates AS (
SELECT date'2015-01-01' d FROM dual union
SELECT date'2015-01-10' d FROM dual union
SELECT date'2015-02-01' d FROM dual union
SELECT timestamp'2015-03-03 23:45:00' d FROM dual union
SELECT timestamp'2015-04-11 12:34:56' d FROM dual    )
SELECT d "Original Date",
trunc(d) "Nearest Day, Time Removed",
trunc(d, 'ww') "Nearest Week",
trunc(d, 'iw') "Start of Week",
trunc(d, 'mm') "Start of Month",
trunc(d, 'year') "Start of Year"   FROM dates;

注意：trunc(d, 'iw') "Start of Week"  在R3中不支持，需替换为trunc(d, 'w') "Start of Week",

7. 本年度最后一秒

SELECT    DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year' - INTERVAL '1 microsecond'