两个日期之间的间隔 [英] Interval between two Dates

问题描述

我有几个关于Oracle日期的问题。

I have a few question about Dates in Oracle.

是否可以得到两个DATE类型之间的间隔（天）？如果是，是否可以在此间隔中为每一天执行语句（例​​如插入）？

Is it possible to get the interval (in days) between two DATE types? If yes, is it possible to do a statement (an insert into for example) for each day in this interval?

我考虑过

while (a_sequence != difference_between_dates) 
LOOP 
a_sequence.next 
-- do things
END LOOP;

是否可能获得具有周期性间隙的间隔？
我想到了像 if（a_sequence％6 || a_sequence％7）作为条件只在周末做事情例如（假设较低的日期总是星期一）。

Is it possible to get that interval with periodic gaps? (Like every week-end for example. Is there a modulo operator in Oracle?) I thought about something like if(a_sequence % 6 || a_sequence % 7) as condition to do things only in week-end for example (assuming the lower date is always Monday).

可以做my_date + 1来获得下一个天？

Is it possible to do my_date+1 to get the next day ?

推荐答案

当然。如果你正在寻找一个PL / SQL解决方案（你可以在纯SQL，但它可能有点难以阅读），像

Sure. If you're looking for a PL/SQL solution (you can do it in pure SQL but it's probably a bit harder to read), something like

DECLARE
  l_first_date date := date '2015-01-01';
  l_last_date  date := date '2015-12-31';
  l_date_to_check date;
BEGIN
  FOR i IN 1 .. l_last_date - l_first_date
  LOOP
    l_date_to_check := l_first_date + i;
    if( to_char( l_date_to_check, 'DY' ) IN ('SAT', 'SUN') )
    then
      <<do_something>>
    end if;
  END LOOP;
END;

这假设您的数据库使用英语语言环境（不同的语言显然有不同的日期缩写） 。你可以通过在 to_char 函数中指定所需的NLS设置来使代码更健壮，但对于大多数系统，这增加了代码的复杂性，需要。

This assumes that your database is using an English language locale (different languages obviously have different abbreviations for days). You can make the code a bit more robust by specifying the NLS settings you want in the to_char function but for most systems, that's adding complexity to the code that is never going to be needed.

这篇关于两个日期之间的间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！