oracle 将 2 个月添加到当前时间戳

Add 2 months to current timestamp

提问by user1968156

How can I add months to a timestamp value in Oracle? In my query, it's getting converted to date value instead:

如何在 Oracle 中为时间戳值添加月份？在我的查询中，它被转换为日期值：

     SELECT add_months(current_timestamp,2) 
     FROM   dual;

The actual output is:

实际输出为：

     ADD_MONTH
     11-MAR-13

The expected output is:

预期的输出是：

    2013-01-01 00:00:00.000000000+00:00

回答by A B

This will give you the date and the timeas a TIMESTAMPdata type:

这将为您提供日期和时间作为TIMESTAMP数据类型：

select TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(SYSDATE, 2), 'YYYYMMDD HH24:MI'), 
'YYYYMMDD HH24:MI') from dual;

If you need more or less precision (E.G. rounding) than what is above, adjust the date formats (both need to be the same format). For example, this will return 2 months down to the seconds level of precision:

如果您需要比上面更多或更少的精度（EG 舍入），请调整日期格式（两者都需要是相同的格式）。例如，这会将 2 个月返回到秒级精度：

select TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(SYSTIMESTAMP, 2), 
'YYYYMMDD HH24:MI:SS'), 'YYYYMMDD HH24:MI:SS') from dual;

This is the closest I can get (as a character) to the format you need:

这是我能得到的（作为字符）最接近您需要的格式：

select TO_CHAR( 
TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(SYSTIMESTAMP, 2), 
'YYYYMMDD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS.FF TZR') from dual;

回答by Mike

I think this will about give you what you're looking for:

我认为这会给你你正在寻找的东西：

SELECT TO_CHAR(TO_TIMESTAMP(ADD_MONTHS(CURRENT_TIMESTAMP,2))
            + (CURRENT_TIMESTAMP - TRUNC(CURRENT_TIMESTAMP)),
       'YYYY-MM-DD HH:MI:SSxFFTZR') FROM DUAL;

The problem with using the interval methods is that you can get an unexpected error depending on the date you run the query. E.g.

使用间隔方法的问题在于，根据运行查询的日期，您可能会遇到意外错误。例如

SELECT TO_TIMESTAMP('31-JAN-2012') + NUMTOYMINTERVAL(1,'MONTH') FROM DUAL;

That query returns:

该查询返回：

ORA-01839: date not valid for month specified

This is because it attempts to return February 31, which is not a valid date.

这是因为它试图返回 2 月 31 日，这是一个无效日期。

ADD_MONTHS is a "safer" way to date math, in that where the interval query would throw an error, ADD_MONTHS will return the last date of the month (Feb 28 or 29 depending on the year) in the above example.

ADD_MONTHS 是一种“更安全”的日期数学方法，因为在上面的示例中，间隔查询会抛出错误，ADD_MONTHS 将返回该月的最后一个日期（2 月 28 日或 29 日，具体取决于年份）。

回答by seawolf

For Oracle:

对于甲骨文：

SELECT
  TIMESTAMP'2014-01-30 08:16:32',                                  -- TS we want to increase by 1 month
--TIMESTAMP'2014-01-30 08:16:32' + NUMTOYMINTERVAL(1, 'MONTH'),    -- raises ORA-01839: date not valid for month specified
--TIMESTAMP'2014-01-30 08:16:32' + INTERVAL '1' MONTH,             -- raises ORA-01839: date not valid for month specified
  ADD_MONTHS(TIMESTAMP'2014-01-30 08:16:32', 1),                   -- works but is a date :(
  CAST(ADD_MONTHS(TIMESTAMP'2014-01-30 08:16:32', 1) AS TIMESTAMP) -- works
FROM DUAL

回答by wolφi

SELECT current_timestamp + INTERVAL '2' MONTH from dual;

To display this in your desired format, use TO_CHAR:

要以您想要的格式显示它，请使用TO_CHAR：

SELECT TO_CHAR(current_timestamp + INTERVAL '2' MONTH, 
       'YYYY-MM-DD HH24:MI:SS.FF9TZH:TZM') from dual;
2013-03-11 23:58:14.789501000+01:00

回答by bonCodigo

For Oracle:

对于甲骨文：

select TO_TIMESTAMP(Sysdate,'DD-Mon-YYYY HH24-MI-SS') + 60
from dual;

select sysdate + interval '2' month from dual;

select TO_TIMESTAMP (Sysdate + interval '2' month,  'DD-Mon-YYYY HH24-MI-SS')
from dual
;

Result1:

结果1：

| TO_TIMESTAMP(SYSDATE,'DD-MON-YYYYHH24-MI-SS')+60 |
----------------------------------------------------
|                     March, 12 0013 00:00:00+0000 |

Result2:

结果2：

|     SYSDATE+INTERVAL'2'MONTH |
--------------------------------
| March, 11 2013 21:41:10+0000 |

Result3:

结果3：

| TO_TIMESTAMP(SYSDATE+INTERVAL'2'MONTH,'DD-MON-YYYYHH24-MI-SS') |
------------------------------------------------------------------
|                                   March, 11 0013 00:00:00+0000 |

• sqlffidle demo.

• sqlffidle 演示。