Trafodion日期运算——DATEADD函数

Trafodion的dateadd函数用法：

DATEADD(datepart, num-expr,datetime-expr)

Datepart：YEAR,MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK。

num-expr：正数、负数均可以。正数表示在原有日期上做加法，负数表示在原有日期上做减法。

datetime-expr：给定的DATE或者TIMESTAMP类型的值。

应用示例：

--2017-12-22的前一天

SQL>select DATEADD(DAY, -1 , DATE'2017-12-22') from dual;

 

(EXPR)

----------

2017-12-21

 

--- 1 row(s) selected.

 

SQL>select DATEADD(DAY, -1,timestamp'2017-12-22 00:00:00') from dual;

 

(EXPR)

-------------------

2017-12-21 00:00:00

 

--- 1 row(s) selected.

 

 

--2017-12-22的下一天

SQL>select  DATEADD(DAY, 1 , DATE'2017-12-22') from dual;

 

(EXPR)

----------

2017-12-23

 

--- 1 row(s) selected.

 

SQL>select DATEADD(DAY, 1,timestamp'2017-12-22 00:00:00') from dual;

 

(EXPR)

-------------------

2017-12-23 00:00:00

 

--- 1 row(s) selected.

 

 

--2017-12-22的上一个月的同一天

SQL>select DATEADD(MONTH, -1 , DATE'2017-12-22') from dual;

 

(EXPR)

----------

2017-11-22

 

--- 1 row(s) selected.

 

SQL>select DATEADD(MONTH, -1,timestamp'2017-12-22 00:00:00') from dual;

 

(EXPR)

-------------------

2017-11-22 00:00:00

 

--- 1 row(s) selected.

 

 

--2017-12-22的下一个月的同一天

SQL>select DATEADD(MONTH, 1 , DATE'2017-12-22') from dual;

 

(EXPR)

----------

2018-01-22

 

--- 1 row(s) selected.

 

SQL>select DATEADD(MONTH, 1,timestamp'2017-12-22 00:00:00') from dual;

 

(EXPR)

-------------------

2018-01-22 00:00:00

 

--- 1 row(s) selected.

 

 

--2017-12-22的上一年的同一天

SQL>select DATEADD(YEAR, -1 , DATE'2017-12-22') from dual;

 

(EXPR)

----------

2016-12-22

 

--- 1 row(s) selected.

 

SQL>select DATEADD(YEAR, -1,timestamp'2017-12-22 00:00:00') from dual;

 

(EXPR)

-------------------

2016-12-22 00:00:00

 

--- 1 row(s) selected.

 

--2017-12-22的下一年的同一天

SQL>select DATEADD(YEAR, 1 , DATE'2017-12-22') from dual;

 

(EXPR)

----------

2018-12-22

 

--- 1 row(s) selected.

 

SQL>select DATEADD(YEAR, 1,timestamp'2017-12-22 00:00:00') from dual;

 

(EXPR)

-------------------

2018-12-22 00:00:00

 

--- 1 row(s) selected.

--linux类型的时间戳1500220712转为trafodion的timestamp

SQL>selectdateadd(SECOND,1500220712,timestamp '1970-01-01 00:00:00') from dual;

 

(EXPR)

---------------------

2017-07-16 15:58:32.0

 

--- 1 row(s) selected.