Re: time series data

From: azwa(at)nc(dot)com(dot)my
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: time series data
Date: 2004-01-29 09:05:29
Message-ID: OFA2A3CD02.659C0824-ONFFFFFF58.004C4587@nc.com.my
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)postgresql(dot)org>
Sent by: pgsql-sql-owner(at)postgresql(dot)org
01/27/2004 10:56 AM CET

To: azwa(at)nc(dot)com(dot)my
cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] time series data

Dnia 2004-01-27 02:42, Użytkownik azwa(at)nc(dot)com(dot)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

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2004-01-29 10:27:51 Re: SQL Query for Top Down fetching of childs
Previous Message Viorel Dragomir 2004-01-29 08:34:26 Re: auto_insert