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 <jasiek@postgresql.org>
Sent by: pgsql-sql-owner@postgresql.org
01/27/2004 10:56 AM CET
To: azwa@nc.com.my cc: pgsql-sql@postgresql.org Subject: Re: [SQL] time series data
Dnia 2004-01-27 02:42, Użytkownik azwa@nc.com.my 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
