X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1C0A4D1C917 for ; Thu, 29 Jan 2004 09:28:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 30251-04 for ; Thu, 29 Jan 2004 05:28:00 -0400 (AST) Received: from domino.nc.com.my (domino.nc.com.my [202.185.214.131]) by svr1.postgresql.org (Postfix) with ESMTP id 60870D1CCDB for ; Thu, 29 Jan 2004 05:27:51 -0400 (AST) Subject: Re: time series data To: pgsql-sql@postgresql.org X-Mailer: Lotus Notes Release 5.0 (Intl) 30 March 1999 Message-ID: From: azwa@nc.com.my Date: Thu, 29 Jan 2004 17:05:29 +0800 X-MIMETrack: Serialize by Router on domino/ANSI(Release 5.0.5 |September 22, 2000) at 01/29/2004 05:05:38 PM, Serialize complete at 01/29/2004 05:05:38 PM MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_alternative 0034340B48256E2A_=" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/248 X-Sequence-Number: 16641 This is a multipart message in MIME format. --=_alternative 0034340B48256E2A_= Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hi,=20 thanks for the feedback. btw i've run the statement below & got the=20 following result : time_key | yr_id | month_id | month_desc | day_id ----------+-------+----------+------------+-------- 193 | 1994 | 7 | jul |= =20=20 13 (1 row) actually if i'm going to have a result as below how could i did in my=20 statement ??? The result should appear as : time_key | yr_id | month_id | month_desc |day_id ----------+-------+----------+----------- 1 | 1994 | 1 | Jan=20 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 =20 =2E =2E =2E the data for day_id should be incremental from 1->31 for each month for=20 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 =20 pls guide /help me to solve the above problem . thanks in advance. Tomasz Myrta 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=BFytkownik azwa@nc.com.my napisa=B3: >=20 >=20 > Hi, >=20 > thanks for the info..btw can u pls explain a little bit detail since= =20 > 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)=20 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 --=_alternative 0034340B48256E2A_= Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hi,  

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
        &nb= sp;     ----------+-------+----------+------------+--------
        &nb= sp;             193 |  1994 |   &nb= sp;           7 |         &nbs= p;   jul        |     13
(1 row)

actually if i'm going to have a= result as below how could i did in my statement ???


        &nb= sp; The result should appear as :

        &nb= sp;              time_key | yr_id | mont= h_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 i= ncremental from 1->31 for each month for specific year. meaning to say E= very month  should have the day_id
from 1---->31 . (follow exac= tly 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
<= /table>
 
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: &nbs= p;      azwa@nc.com.my
        cc: &nbs= p;      pgsql-sql@postgresql.org
        Subject:=        Re: [SQL] time series data



Dnia 2004-01-27 02:42, U=BFytkownik azwa@nc.com.my napisa=B3:
>
>
> Hi,
>
>    thanks for the info..btw can u pls explain a little bit d= etail 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
--=_alternative 0034340B48256E2A_=--