[NOVICE] To get a Table or View like a Calendar with dates

[Csanyi Pal]

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?

[Kevin Grittner]

Post by Csanyi Pal
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

[Csanyi Pal]

Hi Kevin,

Post by Kevin Grittner

Post by Csanyi Pal
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.

[Andreas Kretschmer]

Post by Csanyi Pal
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°

-
Sent via pgsql-novice mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[Csanyi Pal]

Hi Andreas,

Post by Andreas Kretschmer

Post by Csanyi Pal
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
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?