thanks for the feedback. btw i've run the statement below & got the following result :
time_key | yr_id | month_id | month_desc | day_id
----------+-------+----------+------------+--------
193 | 1994 | 7 | jul | 13
(1 row)
actually if i'm going to have a result as below how could i did in my statement ???
The result should appear as :
time_key | yr_id | month_id | month_desc |day_id
----------+-------+----------+-----------
1 | 1994 | 1 | Jan
2 | 1994 | 2 | Feb
3 | 1994 | 3 | Mac
4 | 1994 | 4 | Apr
5 | 1994 | 5 | May
6 | 1994 | 6 | Jun
7 | 1994 | 7 | July
8 | 1994 | 8 | Aug
9 | 1994 | 9 | Sept
10 | 1994 | 10 | Oct
11 | 1994 | 11 | Nov
12 | 1994 | 12 | Dec
.
.
.
the data for day_id should be incremental from 1->31 for each month for specific year. meaning to say Every month should have the day_id
from 1---->31 . (follow exactly the day of the month)
| Time_key | Yr_id | Month_id | Month_desc | Day_id(1-30/31 days) |
|
1
|
1994
|
1
|
Jan
|
1
|
|
2
|
1994
|
1
|
Jan
|
2
|
|
3
|
1994
|
1
|
Jan
|
3
|
|
4
|
1994
|
1
|
Jan
|
4
|
|
5
|
1994
|
1
|
Jan
|
5
|
|
6
|
1994
|
1
|
Jan
|
6
|
|
7
|
1994
|
1
|
Jan
|
7
|
|
8
|
1994
|
1
|
Jan
|
8
|
|
9
|
1994
|
1
|
Jan
|
9 |
pls guide /help me to solve the above problem . thanks in advance.
|
Tomasz Myrta <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 01/27/2004 10:56 AM CET | To: [EMAIL PROTECTED]
cc: [EMAIL PROTECTED] Subject: Re: [SQL] time series data |
Dnia 2004-01-27 02:42, Użytkownik [EMAIL PROTECTED] napisał:
>
>
> Hi,
>
> thanks for the info..btw can u pls explain a little bit detail since
> i can't get thru yr solution.
> thanks in advance
insert into time_table (time_key,year_id,month_id,month_desc,day_id)
select
newid,
extract('year' from your_date),
extract('month' from your_date),
to_char(your_date,'mon'),
extract('day' from your_date)
...
from (select nextval('time_seq') as newid,
'1994-01-01'::date+'1 day'::interval * currval('time_seq') as your_date) x;
Everything you need is to execute query above as many times as you need.
Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
