Re: To get a Table or View like a Calendar with dates

Hi Andreas,

Andreas Kretschmer <akretschmer@spamfence.net> writes:

> Csanyi Pal <csanyipal@gmail.com> wrote:
>
>> OK, I have an initial question.
>>
>> The start date and the end date of the first half part of school year
>> are in two different tables in my database.
>>
>> How can I get rows in a view between those two dates?
>
> Okay, you have the start- and end-date in 2 different tables? No
> problem:
>
> test=*# select * from t_start ;
>      d
> ------------
>  2012-01-01
> (1 row)
>
> Time: 0,196 ms
> test=*# select * from t_end;
>      d
> ------------
>  2012-01-10
> (1 row)
>
> Time: 0,240 ms
> test=*# select (d + s * '1day'::interval)::date from t_start,
> generate_series(0, ((select d from t_end) - (select d from t_start)))s;

I tried the followings:

iskolanaptar_201213=# select (datum + s * '1day'::interval)::date from
felevek_kezdetei_1_8, generate_series(0, ((select datum from
felev1_vege_tan_nap_1_8) - (select datum from felevek_kezdetei_1_8 where
felev1v2 = '1')))s;

and get:

    date
------------
 2012-09-03
 2013-01-15
 2012-09-04
 2013-01-16
 2012-09-05
 2013-01-17
 2012-09-06
 2013-01-18

 ..

 2012-12-19
 2013-05-02
 2012-12-20
 2013-05-03
 2012-12-21
 2013-05-04
(220 rows)

So this isn't what I expected because I get dates from the both half
school years and not just from the first one.

In the table 'felevek_kezdetei_1_8' I have two columns:
'felev1v2' and 'datum':

 felev1v2 |   datum
----------+------------
 1        | 2012-09-03
 2        | 2013-01-15
(2 rows)

The meaning of the 'felev1v2 = 1' is that that the first half of the
school year beginning at 2012-09-03.

To get date intervall only from the first half school year I tried the
command:

iskolanaptar_201213=# select (datum + s * '1day'::interval)::napok from
felevek_kezdetei_1_8 where felev1v2 = '1', generate_series(0, ((select
datum from felev1_vege_tan_nap_1_8) - (select datum from
felevek_kezdetei_1_8 where felev1v2 = '1')))s;
ERROR:  syntax error at or near ","
LINE 1: ...pok from felevek_kezdetei_1_8 where felev1v2 = '1',
generate...

What cause this error and what is the proper command here?

--
Regards from Pal