Inserting Multiple DATE values into a column.

[Tim Davidge]

Hi folks, been a while since I have posted a plea for help and I think I
have forgotten everything I learnt from the helpful contributors to this
newsgroup, that said however :

I'm trying to insert a range of dates for the past say two years into the
first column of say a two column table. I've been trying to use 'current
date' and working backwards from there but for the life of me I cannot get
the syntax right. I can produce a single value, but cannot get a list that
would be all WEEKDAYS in date format for the last two years. Using VALUES
instead of a SELECT statement, how do I get a list instead of a single
value. VALUES doesnt sem to like BETWEEN and I do not know how to use
recursive statements.

SELECT would imply I already have the values in a table, where VALUES as a
function should somehow be able to calculate these for me. Any help would be
greatly appreciated.

TD

[PM \(pm3iinc-nospam\)]

Re: Inserting Multiple DATE values into a column.

WITH
-- GENERATE DATES
TYEARS(DT, CNT, WD) AS
(
-- SEED
VALUES( DATE('2002-01-01'), 1, DAYOFWEEK_ISO(D ATE('2002-01-01')) )
UNION ALL
SELECT TYEARS.DT + 1 DAY, CNT + 1, DAYOFWEEK_ISO(D ATE(TYEARS.DT) + 1 DAY)
FROM TYEARS
-- LIMIT DATA GENERATION TO A CERTAIN TIME LIMIT
WHERE TYEARS.CNT < (366*2)
)
-- DATES WITHOUT THE WEEKENDS
-- (a)
SELECT
RCALC1.DT
FROM
(
SELECT DT FROM TYEARS
WHERE WD NOT IN (6, 7)
) AS RCALC1(DT)
-- WHERE DT BETWEEN ... OR YEAR() ... OR ...
ORDER BY DT
;


You can use an insert instead of the select (a) if you want. (insert into x
select * from ...)
I did not bother about performance...
The statement could be more compact but i did not bother either.

PM

[AK]

Re: Inserting Multiple DATE values into a column.

Tim,

try this:

CREATE TABLE DATE_SEQ(SOME_D ATE DATE NOT NULL);
INSERT INTO DATE_SEQ SELECT DATE('01/01/2003') + (ROW_NUMBER() OVER()
- 1) DAYS FROM SYSCAT.COLUMNS;

[Jeremy Rickard]

Re: Inserting Multiple DATE values into a column.

"Tim Davidge" <tdavidge@no_sp am.hotmail.com> wrote in message news:<jqmdnQDVF MmYsY3dRVn-jw@comcast.com> ...
[color=blue]
> I'm trying to insert a range of dates for the past say two years into the
> first column of say a two column table.[/color]

Recursive query would be the most obvious solution. You'll need to
plug something like the following into an appropriate INSERT (e.g.
maybe joining back to your table to avoid duplicate inserts):

WITH
dates(date) AS
(
VALUES CURRENT DATE - 2 YEARS
UNION ALL
SELECT
date + 1 DAY
FROM
dates
WHERE
date <= CURRENT DATE
)
SELECT date FROM dates WHERE dayofweek_iso(d ate) < 6


Jeremy Rickard

[Tim Davidge]

Re: Inserting Multiple DATE values into a column.

Guys,

That is perfect. Many thanks for the help.

Tim


"Jeremy Rickard" <jrickard@unisy stems.biz> wrote in message
news:d36116ef.0 401221725.2ed48 d65@posting.goo gle.com...[color=blue]
> "Tim Davidge" <tdavidge@no_sp am.hotmail.com> wrote in message[/color]
news:<jqmdnQDVF MmYsY3dRVn-jw@comcast.com> ...[color=blue]
>[color=green]
> > I'm trying to insert a range of dates for the past say two years into[/color][/color]
the[color=blue][color=green]
> > first column of say a two column table.[/color]
>
> Recursive query would be the most obvious solution. You'll need to
> plug something like the following into an appropriate INSERT (e.g.
> maybe joining back to your table to avoid duplicate inserts):
>
> WITH
> dates(date) AS
> (
> VALUES CURRENT DATE - 2 YEARS
> UNION ALL
> SELECT
> date + 1 DAY
> FROM
> dates
> WHERE
> date <= CURRENT DATE
> )
> SELECT date FROM dates WHERE dayofweek_iso(d ate) < 6
>
>
> Jeremy Rickard[/color]

[AK]

Re: Inserting Multiple DATE values into a column.

Jeremy,

I think recursion won't run as fast as my solution.
What do you think?

[PM \(pm3iinc-nospam\)]

Re: Inserting Multiple DATE values into a column.

.... but at least, we, the common table expressioners, will never have to
worry about having enough columns to generate the data we need... ;-)

db2batch -d SAMPLE -f GENDATES.DBB -a USER/USING -i COMPLETE
--#SET PERF_DETAIL 2
--#SET ROWS_FETCH -1
--#SET ROWS_OUT 0

Statement number: 1

SELECT * FROM (SELECT (DATE('01/01/2003') + (ROW_NUMBER() OVER() - 1) DAYS)
R FROM SYSCAT.COLUMNS ) AS R2(DT) WHERE DT <= DATE('01/01/2003') + (3137)
DAYS O
RDER BY DT

DT
------------


Number of rows retrieved is: 3138
Number of rows sent to output is: 0

Prepare Time is: 0.000 seconds
Execute Time is: 0.062 seconds
Fetch Time is: 0.047 seconds
Elapsed Time is: 0.109 seconds

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

Statement number: 2

WITH
TYEARS(DT, CNT, WD) AS
(
VALUES( DATE('2003-01-01'), 1, DAYOFWEEK_ISO(D ATE('2003-01-01')) )
UNION ALL
SELECT TYEARS.DT + 1 DAY, CNT + 1, DAYOFWEEK_ISO(D ATE(TYEARS.DT) + 1 DAY)
FROM TYEARS
WHERE TYEARS.CNT < (366*12)
)
SELECT
RCALC1.DT
FROM
(
SELECT DT FROM TYEARS
WHERE WD NOT IN (6, 7)
) AS RCALC1(DT)
ORDER BY DT


DT
------------


Number of rows retrieved is: 3138
Number of rows sent to output is: 0

Prepare Time is: 0.000 seconds
Execute Time is: 0.407 seconds
Fetch Time is: 0.046 seconds
Elapsed Time is: 0.453 seconds

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



Summary of Results
=============== ===
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched Printed
1 0.109 0.063 3138 0
2 0.453 0.406 3138 0

Arith. mean 0.281 0.234
Geom. mean 0.222 0.159

PM

"AK" <ak_tiredofspam @yahoo.com> a écrit dans le message de
news:46e627da.0 401230632.660ed 0af@posting.goo gle.com...[color=blue]
> Jeremy,
>
> I think recursion won't run as fast as my solution.
> What do you think?[/color]

[Jeremy Rickard]

Re: Inserting Multiple DATE values into a column.

ak_tiredofspam@ yahoo.com (AK) wrote in message news:<46e627da. 0401230632.660e d0af@posting.go ogle.com>...[color=blue]
> Jeremy,
>
> I think recursion won't run as fast as my solution.
> What do you think?[/color]

Slightly slower, yes, but on my P3 laptop the recursive solution can
still generate almost 200 years of weekdays in a second (measured with
db2batch). I don't think the OP was planning to populate his table
more than once per day.

Surely more important, the recursive solutions didn't rely on having
an existing table with sufficient rows.


Jeremy

[AK]

Re: Inserting Multiple DATE values into a column.

> Surely more important, the recursive solutions didn't rely on having[color=blue]
> an existing table with sufficient rows.[/color]

I see and respect your point,
but even when you have just created a new database, it already has
system tables. Enough columns for most practical cases.

[Knut Stolze]

Re: Inserting Multiple DATE values into a column.

AK <ak_tiredofspam @yahoo.com> wrote:
[color=blue][color=green]
>> Surely more important, the recursive solutions didn't rely on having
>> an existing table with sufficient rows.[/color]
>
> I see and respect your point,
> but even when you have just created a new database, it already has
> system tables. Enough columns for most practical cases.[/color]

That depends on your "practical case", I'd say.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

Re: Inserting Multiple DATE values into a column.

Knut,

the original poster has specified: "I'm trying to insert a range of
dates for the past say two years into the"

two years < 1K days < number of rows in SYSCAT.COLUMNS in an empty
database

Regarding "what if more rows are needed" concerns, I have an
impression that performance of recursion might degrade exponencially
when number of rows increases.

Please correct me if I'm wrong

[PM \(pm3iinc-nospam\)]

Re: Inserting Multiple DATE values into a column.

Looks linear to me in this case.
I did the recursion for 1-100 years and 1000 years.
STMT NO (nb years), elapse, agent, rows fetch, elapse/year, agent/year,
fetch/year



1 0.077 0.063 262 0.08 0.06 262.00

2 0.095 0.109 523 0.05 0.05 261.50

3 0.14 0.125 784 0.05 0.04 261.33

4 0.156 0.141 1046 0.04 0.04 261.50

5 0.202 0.188 1308 0.04 0.04 261.60

6 0.234 0.203 1568 0.04 0.03 261.33

7 0.281 0.25 1830 0.04 0.04 261.43

8 0.313 0.281 2092 0.04 0.04 261.50

9 0.359 0.328 2353 0.04 0.04 261.44

10 0.39 0.344 2614 0.04 0.03 261.40

11 0.438 0.391 2876 0.04 0.04 261.45

12 0.454 0.406 3138 0.04 0.03 261.50

13 0.484 0.438 3398 0.04 0.03 261.38

14 0.547 0.5 3660 0.04 0.04 261.43

15 0.579 0.531 3922 0.04 0.04 261.47

16 0.609 0.547 4183 0.04 0.03 261.44

17 0.656 0.594 4444 0.04 0.03 261.41

18 0.687 0.609 4706 0.04 0.03 261.44

19 0.766 0.656 4968 0.04 0.03 261.47

20 0.766 0.688 5228 0.04 0.03 261.40

21 0.829 0.75 5490 0.04 0.04 261.43

22 0.843 0.75 5752 0.04 0.03 261.45

23 0.891 0.813 6013 0.04 0.04 261.43

24 0.907 0.828 6274 0.04 0.03 261.42

25 0.969 0.859 6536 0.04 0.03 261.44

26 1 0.891 6798 0.04 0.03 261.46

27 1.015 0.922 7058 0.04 0.03 261.41

28 1.079 0.984 7320 0.04 0.04 261.43

29 1.126 1 7582 0.04 0.03 261.45

30 1.157 1.031 7843 0.04 0.03 261.43

31 1.172 1.063 8104 0.04 0.03 261.42

32 1.219 1.125 8366 0.04 0.04 261.44

33 1.234 1.141 8628 0.04 0.03 261.45

34 1.282 1.141 8888 0.04 0.03 261.41

35 1.297 1.156 9150 0.04 0.03 261.43

36 1.374 1.219 9412 0.04 0.03 261.44

37 1.47 1.328 9673 0.04 0.04 261.43

38 1.485 1.297 9934 0.04 0.03 261.42

39 1.485 1.313 10196 0.04 0.03 261.44

40 1.562 1.391 10458 0.04 0.03 261.45

41 1.578 1.391 10718 0.04 0.03 261.41

42 1.64 1.438 10980 0.04 0.03 261.43

43 1.656 1.469 11242 0.04 0.03 261.44

44 1.672 1.484 11503 0.04 0.03 261.43

45 1.704 1.5 11764 0.04 0.03 261.42

46 1.735 1.547 12026 0.04 0.03 261.43

47 1.75 1.594 12288 0.04 0.03 261.45

48 1.797 1.609 12548 0.04 0.03 261.42

49 1.843 1.656 12810 0.04 0.03 261.43

50 1.875 1.672 13072 0.04 0.03 261.44

51 1.937 1.703 13333 0.04 0.03 261.43

52 1.969 1.75 13594 0.04 0.03 261.42

53 1.984 1.781 13856 0.04 0.03 261.43

54 2.078 1.828 14118 0.04 0.03 261.44

55 2.062 1.844 14378 0.04 0.03 261.42

56 2.141 1.906 14640 0.04 0.03 261.43

57 2.172 1.953 14902 0.04 0.03 261.44

58 2.171 1.953 15163 0.04 0.03 261.43

59 2.251 2 15424 0.04 0.03 261.42

60 2.265 2.016 15686 0.04 0.03 261.43

61 2.282 2.031 15948 0.04 0.03 261.44

62 2.344 2.109 16208 0.04 0.03 261.42

63 2.407 2.156 16470 0.04 0.03 261.43

64 2.39 2.156 16732 0.04 0.03 261.44

65 2.375 2.125 16993 0.04 0.03 261.43

66 2.468 2.172 17254 0.04 0.03 261.42

67 2.5 2.234 17516 0.04 0.03 261.43

68 2.563 2.281 17778 0.04 0.03 261.44

69 2.579 2.297 18038 0.04 0.03 261.42

70 2.672 2.344 18300 0.04 0.03 261.43

71 2.719 2.391 18562 0.04 0.03 261.44

72 2.719 2.406 18823 0.04 0.03 261.43

73 2.782 2.469 19084 0.04 0.03 261.42

74 2.796 2.469 19346 0.04 0.03 261.43

75 2.797 2.484 19608 0.04 0.03 261.44

76 2.843 2.547 19868 0.04 0.03 261.42

77 2.922 2.563 20130 0.04 0.03 261.43

78 3 2.688 20392 0.04 0.03 261.44

79 2.953 2.656 20653 0.04 0.03 261.43

80 2.969 2.641 20914 0.04 0.03 261.43

81 3.047 2.719 21176 0.04 0.03 261.43

82 3.094 2.766 21438 0.04 0.03 261.44

83 3.156 2.813 21698 0.04 0.03 261.42

84 3.141 2.813 21960 0.04 0.03 261.43

85 3.156 2.766 22222 0.04 0.03 261.44

86 3.266 2.906 22483 0.04 0.03 261.43

87 3.234 2.875 22744 0.04 0.03 261.43

88 3.328 2.953 23006 0.04 0.03 261.43

89 3.391 3 23268 0.04 0.03 261.44

90 3.437 3.063 23528 0.04 0.03 261.42

91 3.438 3.047 23790 0.04 0.03 261.43

92 3.531 3.125 24052 0.04 0.03 261.43

93 3.579 3.188 24313 0.04 0.03 261.43

94 3.532 3.203 24574 0.04 0.03 261.43

95 3.61 3.172 24836 0.04 0.03 261.43

96 3.735 3.266 25098 0.04 0.03 261.44

97 3.719 3.313 25358 0.04 0.03 261.42

98 3.781 3.344 25620 0.04 0.03 261.43

99 3.781 3.344 25882 0.04 0.03 261.43

100 3.922 3.469 26143 0.04 0.03 261.43

1000 37.53 33.125 261428 0.04 0.03 261.43

PM



"AK" <ak_tiredofspam @yahoo.com> a écrit dans le message de
news:46e627da.0 401260539.12ce1 243@posting.goo gle.com...[color=blue]
> Knut,
>
> the original poster has specified: "I'm trying to insert a range of
> dates for the past say two years into the"
>
> two years < 1K days < number of rows in SYSCAT.COLUMNS in an empty
> database
>
> Regarding "what if more rows are needed" concerns, I have an
> impression that performance of recursion might degrade exponencially
> when number of rows increases.
>
> Please correct me if I'm wrong[/color]

[PM \(pm3iinc-nospam\)]

Re: Inserting Multiple DATE values into a column.

Testing table format

STMT NO
elapse
agent
rows fetch
elapse/year
agent/year
fetch/year

1
0.077
0.063
262
0.08
0.06
262.00

2
0.095
0.109
523
0.05
0.05
261.50

3
0.14
0.125
784
0.05
0.04
261.33

4
0.156
0.141
1046
0.04
0.04
261.50

5
0.202
0.188
1308
0.04
0.04
261.60

6
0.234
0.203
1568
0.04
0.03
261.33

7
0.281
0.25
1830
0.04
0.04
261.43

8
0.313
0.281
2092
0.04
0.04
261.50

9
0.359
0.328
2353
0.04
0.04
261.44

10
0.39
0.344
2614
0.04
0.03
261.40

11
0.438
0.391
2876
0.04
0.04
261.45

12
0.454
0.406
3138
0.04
0.03
261.50

13
0.484
0.438
3398
0.04
0.03
261.38

14
0.547
0.5
3660
0.04
0.04
261.43

15
0.579
0.531
3922
0.04
0.04
261.47

16
0.609
0.547
4183
0.04
0.03
261.44

17
0.656
0.594
4444
0.04
0.03
261.41

18
0.687
0.609
4706
0.04
0.03
261.44

19
0.766
0.656
4968
0.04
0.03
261.47

20
0.766
0.688
5228
0.04
0.03
261.40

21
0.829
0.75
5490
0.04
0.04
261.43

22
0.843
0.75
5752
0.04
0.03
261.45

23
0.891
0.813
6013
0.04
0.04
261.43

24
0.907
0.828
6274
0.04
0.03
261.42

25
0.969
0.859
6536
0.04
0.03
261.44

26
1
0.891
6798
0.04
0.03
261.46

27
1.015
0.922
7058
0.04
0.03
261.41

28
1.079
0.984
7320
0.04
0.04
261.43

29
1.126
1
7582
0.04
0.03
261.45

30
1.157
1.031
7843
0.04
0.03
261.43

31
1.172
1.063
8104
0.04
0.03
261.42

32
1.219
1.125
8366
0.04
0.04
261.44

33
1.234
1.141
8628
0.04
0.03
261.45

34
1.282
1.141
8888
0.04
0.03
261.41

35
1.297
1.156
9150
0.04
0.03
261.43

36
1.374
1.219
9412
0.04
0.03
261.44

37
1.47
1.328
9673
0.04
0.04
261.43

38
1.485
1.297
9934
0.04
0.03
261.42

39
1.485
1.313
10196
0.04
0.03
261.44

40
1.562
1.391
10458
0.04
0.03
261.45

41
1.578
1.391
10718
0.04
0.03
261.41

42
1.64
1.438
10980
0.04
0.03
261.43

43
1.656
1.469
11242
0.04
0.03
261.44

44
1.672
1.484
11503
0.04
0.03
261.43

45
1.704
1.5
11764
0.04
0.03
261.42

46
1.735
1.547
12026
0.04
0.03
261.43

47
1.75
1.594
12288
0.04
0.03
261.45

48
1.797
1.609
12548
0.04
0.03
261.42

49
1.843
1.656
12810
0.04
0.03
261.43

50
1.875
1.672
13072
0.04
0.03
261.44

51
1.937
1.703
13333
0.04
0.03
261.43

52
1.969
1.75
13594
0.04
0.03
261.42

53
1.984
1.781
13856
0.04
0.03
261.43

54
2.078
1.828
14118
0.04
0.03
261.44

55
2.062
1.844
14378
0.04
0.03
261.42

56
2.141
1.906
14640
0.04
0.03
261.43

57
2.172
1.953
14902
0.04
0.03
261.44

58
2.171
1.953
15163
0.04
0.03
261.43

59
2.251
2
15424
0.04
0.03
261.42

60
2.265
2.016
15686
0.04
0.03
261.43

61
2.282
2.031
15948
0.04
0.03
261.44

62
2.344
2.109
16208
0.04
0.03
261.42

63
2.407
2.156
16470
0.04
0.03
261.43

64
2.39
2.156
16732
0.04
0.03
261.44

65
2.375
2.125
16993
0.04
0.03
261.43

66
2.468
2.172
17254
0.04
0.03
261.42

67
2.5
2.234
17516
0.04
0.03
261.43

68
2.563
2.281
17778
0.04
0.03
261.44

69
2.579
2.297
18038
0.04
0.03
261.42

70
2.672
2.344
18300
0.04
0.03
261.43

71
2.719
2.391
18562
0.04
0.03
261.44

72
2.719
2.406
18823
0.04
0.03
261.43

73
2.782
2.469
19084
0.04
0.03
261.42

74
2.796
2.469
19346
0.04
0.03
261.43

75
2.797
2.484
19608
0.04
0.03
261.44

76
2.843
2.547
19868
0.04
0.03
261.42

77
2.922
2.563
20130
0.04
0.03
261.43

78
3
2.688
20392
0.04
0.03
261.44

79
2.953
2.656
20653
0.04
0.03
261.43

80
2.969
2.641
20914
0.04
0.03
261.43

81
3.047
2.719
21176
0.04
0.03
261.43

82
3.094
2.766
21438
0.04
0.03
261.44

83
3.156
2.813
21698
0.04
0.03
261.42

84
3.141
2.813
21960
0.04
0.03
261.43

85
3.156
2.766
22222
0.04
0.03
261.44

86
3.266
2.906
22483
0.04
0.03
261.43

87
3.234
2.875
22744
0.04
0.03
261.43

88
3.328
2.953
23006
0.04
0.03
261.43

89
3.391
3
23268
0.04
0.03
261.44

90
3.437
3.063
23528
0.04
0.03
261.42

91
3.438
3.047
23790
0.04
0.03
261.43

92
3.531
3.125
24052
0.04
0.03
261.43

93
3.579
3.188
24313
0.04
0.03
261.43

94
3.532
3.203
24574
0.04
0.03
261.43

95
3.61
3.172
24836
0.04
0.03
261.43

96
3.735
3.266
25098
0.04
0.03
261.44

97
3.719
3.313
25358
0.04
0.03
261.42

98
3.781
3.344
25620
0.04
0.03
261.43

99
3.781
3.344
25882
0.04
0.03
261.43

100
3.922
3.469
26143
0.04
0.03
261.43

1000
37.53
33.125
261428
0.04
0.03
261.43



PM

[AK]

Re: Inserting Multiple DATE values into a column.

PM,

thank you for the correction

[Knut Stolze]

Re: Inserting Multiple DATE values into a column.

AK <ak_tiredofspam @yahoo.com> wrote:
[color=blue]
> Knut,
>
> the original poster has specified: "I'm trying to insert a range of
> dates for the past say two years into the"
>
> two years < 1K days < number of rows in SYSCAT.COLUMNS in an empty
> database[/color]

I don't argue against that.
But still I would personally not rely on it. Maybe some change in the
application logic requires now 4 years and then IBM might change something
in the DB2 catalog (e.g. maybe not externalizing the SYSIBM tables - who
knows) and the scheme doesn't work any longer in a predictable way.
[color=blue]
> Regarding "what if more rows are needed" concerns, I have an
> impression that performance of recursion might degrade exponencially
> when number of rows increases.[/color]

Possibly not, as PM demonstrated.

Besides, you could write your own table function that takes a start and end
date and calculates all the dates in between. Its trivial to implement and
has a linear run time. For my personal taste, this would be much safer.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena