Обсуждение: To get a Table or View like a Calendar with dates

Hi,

I'm trying to explain what I want to achieve with my postgresql
database.

The database should store the data like dates of the beginning and the
end of the school year, holidays, non school days, various events, etc.

I want to get a Table or a View which I can use as a School Calendar
with school days, and non school days too, and much more.

I have created Tables with dates of the beginning date and the end date
of the school year.

I have Table with dates of holidays and/or non school days in the school
year too.

Can I create a Table or a View from these Tables to get such a school
calendar?

--
Regards from Pal

Csanyi Pal <csanyipal@gmail.com> wrote:

> The database should store the data like dates of the beginning and
> the end of the school year, holidays, non school days, various
> events, etc.
>
> I want to get a Table or a View which I can use as a School
> Calendar with school days, and non school days too, and much more.
>
> I have created Tables with dates of the beginning date and the end
> date of the school year.
>
> I have Table with dates of holidays and/or non school days in the
> school year too.
>
> Can I create a Table or a View from these Tables to get such a
> school calendar?

Yes, it sounds like the set of tables you describe could support a
great many useful queries, and you could encapsulate these in views
to make them easier to use.

Are you having some problem doing so?  What have you tried?  What
did you expect to happen?  What happened instead?

-Kevin

Hi Kevin,

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

> Csanyi Pal <csanyipal@gmail.com> wrote:
>
>> The database should store the data like dates of the beginning and
>> the end of the school year, holidays, non school days, various
>> events, etc.
>>
>> I want to get a Table or a View which I can use as a School
>> Calendar with school days, and non school days too, and much more.
>>
>> I have created Tables with dates of the beginning date and the end
>> date of the school year.
>>
>> I have Table with dates of holidays and/or non school days in the
>> school year too.
>>
>> Can I create a Table or a View from these Tables to get such a
>> school calendar?
>
> Yes, it sounds like the set of tables you describe could support a
> great many useful queries, and you could encapsulate these in views
> to make them easier to use.
>
> Are you having some problem doing so?  What have you tried?  What
> did you expect to happen?  What happened instead?

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?

I expect to get rows like:
date1 weekday1
date2 weekday2
 ..
datex weekdayx

where 'date1' should to be the first day in the school year and 'datex'
should to be the last day in the first part of school year.

If I can get these rows in a view then I can after that modify the view
so I get extended informations like holidays if such occures in that
range of dates, etc.

--
Regards from Pal

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;
    date
------------
 2012-01-01
 2012-01-02
 2012-01-03
 2012-01-04
 2012-01-05
 2012-01-06
 2012-01-07
 2012-01-08
 2012-01-09
 2012-01-10
(10 rows)





Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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

Hi Kevin,

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

> Csanyi Pal <csanyipal@gmail.com> wrote:
>
>> The database should store the data like dates of the beginning and
>> the end of the school year, holidays, non school days, various
>> events, etc.
>>
>> I want to get a Table or a View which I can use as a School
>> Calendar with school days, and non school days too, and much more.
>>
>> I have created Tables with dates of the beginning date and the end
>> date of the school year.
>>
>> I have Table with dates of holidays and/or non school days in the
>> school year too.
>>
>> Can I create a Table or a View from these Tables to get such a
>> school calendar?
>
> Yes, it sounds like the set of tables you describe could support a
> great many useful queries, and you could encapsulate these in views
> to make them easier to use.

I have amongs other two tables:
felevek_kezdetei_1_8 and
felev1_vege_tan_nap_1_8

iskolanaptar_201213=# set search_path to félévek_negyedévek;
SET

The first table contains the ordinal number of the semester (1 or 2) and
the start dates of the same:

iskolanaptar_201213=# select * from felevek_kezdetei_1_8;
 felev1v2 |   datum
----------+------------
 1        | 2012-09-03
 2        | 2013-01-15
(2 rows)

The second table contains the end date of the first semester and the
number of the days in this semester:

iskolanaptar_201213=# select * from felev1_vege_tan_nap_1_8;
   datum    | ennyi_tan_nap
------------+---------------
 2012-12-21 |            78
(1 row)

So I'm trying as a beginning to create the 'felev_1_napjai' view that
should display only the first and the end date of the first semester:

iskolanaptar_201213=# create or replace view felev_1_napjai as select
datum from felevek_kezdetei_1_8, felev1_vege_tan_nap_1_8 where
felevek_kezdetei_1_8.felev1v2 = '1';
ERROR:  column reference "datum" is ambiguous
LINE 1: create or replace view felev_1_napjai as select datum from f
                                                        ^
> Are you having some problem doing so?  What have you tried?  What
> did you expect to happen?  What happened instead?

As you can see above I get error message.

If I success in this I shall modify this view so so I get a view of
dates between the start and the end date of the first semester. I shall
to use the generate_series somehow, but sofar I have no success.

--
Regards from Pal

Csanyi Pal <csanyipal@gmail.com> wrote:

> The first table contains the ordinal number of the semester (1 or
> 2) and the start dates of the same:
>
> iskolanaptar_201213=# select * from felevek_kezdetei_1_8;
>  felev1v2 |   datum
> ----------+------------
>  1        | 2012-09-03
>  2        | 2013-01-15
> (2 rows)
>
> The second table contains the end date of the first semester and
> the number of the days in this semester:
>
> iskolanaptar_201213=# select * from felev1_vege_tan_nap_1_8;
>    datum    | ennyi_tan_nap
> ------------+---------------
>  2012-12-21 |            78
> (1 row)

I think you need to rethink the table structure.  It's not
immediately obvious why you wouldn't include the date of the end of
the semester in the same table as the start date for the semester
and the semester identifying information.  You might want to read up
on data normalization for relational databases.  The usual way of
organizing something like semester information would be to have a
table with the columns which identify a semester (perhaps school
year and semester number) and all the data elements which require
both of those elements, and only those elements, to determine the
correct value.  That would probably include start date and end date
for the semester.  (Or if you can start development with 9.2 beta at
this point, it might be even better to use a date range instead of
the two dates.)

You'll be in much better shape if you organize your data that way.
If you review the literature, you want to shoot for "3rd normal
form" -- below that level things tend to be pretty messy; above that
can get pretty esoteric.

-Kevin