Difference between revisions of "DATE GENERATOR"

From Agri4castWiki
Jump to: navigation, search
Line 174: Line 174:
  
 
<pre>
 
<pre>
DAY      DAYNO_WK    WEEK DAYNO_MON MONTH DAYNO_YR YEAR  DAYNO_DEC DECADE PERIODNO    DEC_MONTH JULIAN REL_DAYNO
+
DAY      DAYNO_WK    WEEK DAYNO_MON MONTH DAYNO_YR YEAR  DAYNO_DEC DECADE PERIODNO    DEC_MONTH JULIAN REL_DAYNO
 
10-JAN-01 3 2 10 1 10 2001 10 1 Jan I 2451920 0
 
10-JAN-01 3 2 10 1 10 2001 10 1 Jan I 2451920 0
 
20-JAN-01 6 3 20 1 20 2001 10 2 Jan II 2451930 10
 
20-JAN-01 6 3 20 1 20 2001 10 2 Jan II 2451930 10

Revision as of 18:35, 31 January 2011



Introduction

The date generator is a tool to generate lists of dates. An easy example is a list of consecutive days:

  • 01 jan 2000
  • 02 jan 2000
  • 03 jan 2000
  • ...
  • 31 dec 2000

This list of days starts in the year 2000 (on January 1) and ends also in 2000 (on December 31) producing all days in between. In other words, the list is limited by the year to begin with (2000) and the year to end (2000), stepping per day with a step size of 1 day. Of course, other limits, steps and step sizes are possible. You can add some more attributes to the list of dates like the week, month, year or dekad of the date. The date generator follows the international ISO standards with regard to week number and the start of the week.

Potential use is diverse. In MARSOP3 the date generator is used in applications like aggregations of indicators (e.g. yield-, weather-) over geographical units and time series. As the date generator produces a list like a table comparable with the traditional ones in a database you use the generator preferably in a query. The date generator is only available as a package in the oracle database. To get the date generator working, follow the instructions.

Interface

In order to get the desired list you will have to specify it's limits and contents in the main function of the date generator: dates. The table below summarizes the parameter list of the function dates. All parameters have a default value which may be used to generate a simple, standard list.

Parameter list for date generator.dates
parameter domain meaning default value, meaning
p_yr2start integer, range 1 .. 9999 the year to start with, the list starts with the first day of that year null: the current year will be chosen
p_yr2end integer, range 1 .. 9999 the year to end, the list ends with the last day of that year null: the current year will be chosen
p_dy2start date, range 01010001 .. 31129999 the day to start with, a value for this parameter overrules p_yr2start null: no meaning
p_dy2end date, range 01010001 .. 31129999 the day to end, a value for this parameter overrules p_yr2end null: no meaning
p_amnt_days integer the amount of dates to take into account, a value for this parameter overrules other limiting parameters null: no meaning
p_date_flags date indicator - see list below a list of one or more attributes, the flags must be concatenated with a '+'. Note that the parameter must be proceeded with the prefix date_generator (see also examples) null: ind_date (this flag can not be omitted)
p_step step indicator - see list below the interval to generate the date list, e.g. day, dekad, month, etc. Note that the parameter must be proceeded with the prefix date_generator (see also examples) null: step_day
p_stepsize integer, range 1 .. the number of steps to take into account before generating the next date (for instance 2 means that each second date is omitted) null: stepsize = 1
p_stepmethod method indicator - see list below one of the methods to determine the exact value of the next date in the list (see example for generating the end date of dekads). Note that the parameter must be proceeded with the prefix date_generator (see also examples) null: keep_dayno




Examples

If on the 20-th of January 2011 you compose the query, without specifying any parameter:

select d.day
from table(date_generator.dates()) d

it will produce a list of dates of the current year: 2011 starting with 1 january and ending with 31 december, containing only dates (the default column) listing each date (step is day, step size is 1).

Next the query:

select d.*
from table(date_generator.dates
            (2000, 2000, null, null, null
            ,date_generator.ind_date + date_generator.ind_week + date_generator.ind_dayno_wk
            ,date_generator.step_week, 1
            )
          ) d

will produce a list of 53 dates in the year 2000, with attributes: date, number of day in the week, week number in tge year 2000. The list starts with date: 1 january; day_no: 6; week: 52 (which is according ISO standard).


Queries that return dates with interval dekad have two methods in determining the last date. For instance query:

select *
from table(date_generator.dates
            (null,null,to_date('10012001','ddmmyyyy'),to_date('31122001','ddmmyyyy'),null
            ,date_generator.ind_all
            ,date_generator.step_decade,1,date_generator.keep_lastday))

will produce a list of 36 dates in the year 2001, with all attributes. Note that the last date of a dekad is given (parameter p_stepmethod has value keep_lastday). For dekad 3 this is January 31 while for dekad 6 this February 28:

DAY      DAYNO_WK    WEEK DAYNO_MON MONTH DAYNO_YR YEAR  DAYNO_DEC DECADE PERIODNO    DEC_MONTH  JULIAN REL_DAYNO
10-JAN-01	3	2        10	1	10 2001		10	1		Jan I	2451920	0
20-JAN-01	6	3        20	1	20 2001		10	2		Jan II	2451930	10
31-JAN-01	3	5        31	1	31 2001		11	3		Jan III	2451941	21 
10-FEB-01	6	6        10	2	41 2001		10	4		Feb I	2451951	31
20-FEB-01	2	8        20	2	51 2001		10	5		Feb II	2451961	41
28-FEB-01	3	9        28	2	59 2001		8	6		Feb III	2451969	49

but query

select *
from table(date_generator.dates
            (null,null,to_date('10012001','ddmmyyyy'),to_date('31122001','ddmmyyyy'),null
            ,date_generator.ind_all
            ,date_generator.step_decade,1,date_generator.keep_dayno))

will produce a list of 36 dates in the year 2001 where not the last date of a dekad is given but each tenth day of a dekad (parameter p_stepmethod has value keep_dayno). For dekad 3 this is January 30 while for dekad 6 this it remains February 28:

DAY      DAYNO_WK    WEEK DAYNO_MON MONTH DAYNO_YR YEAR  DAYNO_DEC DECADE PERIODNO    DEC_MONTH  JULIAN REL_DAYNO
10-JAN-01	3	2	10	1	10 2001		10	1		Jan I	2451920	0
20-JAN-01	6	3	20	1	20 2001		10	2		Jan II	2451930	10
30-JAN-01	2	5	30	1	30 2001		10	3		Jan III	2451940	20
10-FEB-01	6	6	10	2	41 2001		10	4		Feb I	2451951	31
20-FEB-01	2	8	20	2	51 2001		10	5		Feb II	2451961	41
28-FEB-01	3	9	28	2	59 2001		8	6		Feb III	2451969	49