Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group