DATE GENERATOR

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

List of possible values for parameter p_date_flags

value meaning ind_date when selected, dates are generated ind_dayno_wk when selected, the number of the day in the week is generated ind_week when selected, the number of the week in the year is generated ind_dayno_mon when selected, the number of the day in the month is generated ind_month when selected, the number of the month in the year is generated ind_dayno_yr when selected, the number of the day in the year is generated ind_year when selected, the year is generated ind_dayno_dec when selected, the number of the day within the dekad is generated ind_decade when selected, the number of the dekad in the year is generated ind_dec_month when selected, the month and the dekad within the month is generated ind_julian when selected, the julian day number is generated (see Julian day) ind_rel_dayno when selected, the offset of the date from the first date in the list, counted as number of days ind_all when selected, all the attributes of the date are generated

List of possible values for parameter p_steps

value meaning step_day when selected, it uses an interval of one day to generate the next date step_week when selected, it uses an interval of one week to generate the next date step_decade when selected, it uses an interval of one dekad to generate the next date step_month when selected, it uses an interval of one month to generate the next date step_year when selected, it uses an interval of one year to generate the next date

List of possible values for parameter p_stepmethod
value meaning keep_dayno when selected, it generates a next date with the same relative number of the day in the interval as the first date in the list keep_lastday when selected, it generates a next date with the highest relative number of the day in the interval, this is only relevant in cases that more dates are possible at the end of dekads (see example for generating the end date of dekads)

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