From: azwa@nc.com.my
To: pgsql-sql@postgresql.org
Subject: Re: time series data
Date: Thu, 29 Jan 2004 17:05:29 +0800
Message-ID: <OFA2A3CD02.659C0824-ONFFFFFF58.004C4587@nc.com.my> (raw)
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
----------+-------+----------+------------+--------
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 <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: azwa@nc.com.my
Subject: Re: time series data
In-Reply-To: <OFA2A3CD02.659C0824-ONFFFFFF58.004C4587@nc.com.my>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox