Re: time series data - Mailing list pgsql-sql
| From | azwa@nc.com.my |
|---|---|
| Subject | Re: time series data |
| Date | |
| Msg-id | OFA2A3CD02.659C0824-ONFFFFFF58.004C4587@nc.com.my Whole thread Raw |
| In response to | time series data (azwa@nc.com.my) |
| List | pgsql-sql |
<font face="Times New Roman" size="2">Hi, </font><br /><br /><font face="Times New Roman" size="2">thanks for the
feedback.btw i've run the statement below & got the following result :</font><br /><br /><font face="Times New
Roman"size="2"> time_key | yr_id | month_id | month_desc | day_id</font><br /><font face="Times New Roman"
size="2"> ----------+-------+----------+------------+--------</font><br /><font face="Times New Roman"
size="2"> 193 | 1994 | 7 | jul | 13</font><br /><font
face="TimesNew Roman" size="2">(1 row)</font><br /><br /><font face="Times New Roman" size="2">actually if i'm going to
havea result as below how could i did in my statement ???</font><br /><br /><br /><font face="Times New Roman"
size="2"> The result should appear as :</font><br /><br /><font face="Times New Roman" size="2">
time_key | yr_id | month_id | month_desc |day_id</font><br /><font face="Times New Roman"
size="2">----------+-------+----------+-----------</font><br/><font face="Times New Roman" size="2"> 1 | 1994 |
1 | Jan </font><br /><font face="Times New Roman" size="2"> 2 | 1994 | 2 |
Feb</font><br/><font face="Times New Roman" size="2"> 3 | 1994 | 3 | Mac</font><br /><font face="Times
NewRoman" size="2"> 4 | 1994 | 4 | Apr</font><br /><font face="Times New Roman" size="2"> 5 |
1994| 5 | May</font><br /><font face="Times New Roman" size="2"> 6 | 1994 | 6 | Jun</font><br
/><fontface="Times New Roman" size="2"> 7 | 1994 | 7 | July</font><br /><font face="Times New Roman"
size="2"> 8 | 1994 | 8 | Aug</font><br /><font face="Times New Roman" size="2"> 9 | 1994 |
9| Sept</font><br /><font face="Times New Roman" size="2"> 10 | 1994 | 10 | Oct</font><br /><font
face="TimesNew Roman" size="2"> 11 | 1994 | 11 | Nov</font><br /><font face="Times New Roman" size="2">
12 | 1994 | 12 | Dec</font><br /><font face="Times New Roman" size="2"> </font><br /><font face="Times
NewRoman" size="2">.</font><br /><font face="Times New Roman" size="2">.</font><br /><font face="Times New Roman"
size="2">.</font><br/><font face="Times New Roman" size="2">the data for day_id should be incremental from 1->31 for
eachmonth for specific year. meaning to say Every month should have the day_id</font><br /><font face="Times New
Roman"size="2">from 1---->31 . (follow exactly the day of the month)</font><br /><table border="border"><tr
valign="top"><td><fontface="Times New Roman" size="2"> Time_key</font></td><td><font face="Times New Roman" size="2">
Yr_id</font></td><td><fontface="Times New Roman" size="2"> Month_id</font></td><td><font face="Times New Roman"
size="2"> Month_desc</font></td><td><font face="Times New Roman" size="2"> Day_id(1-30/31 days)</font></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">1</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">1</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">2</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">2</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">3</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">3</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">4</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">4</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">5</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">5</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">6</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">6</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">7</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">7</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">8</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman" size="2">8</font></div></td></tr><tr
valign="top"><td><divalign="center"><font face="Times New Roman" size="2">9</font></div></td><td><div
align="center"><fontface="Times New Roman" size="2">1994</font></div></td><td><div align="center"><font face="Times New
Roman"size="2">1</font></div></td><td><div align="center"><font face="Times New Roman"
size="2">Jan</font></div></td><td><divalign="center"><font face="Times New Roman"
size="2">9</font></div></td></tr></table><br/><font face="Times New Roman" size="2"> </font><br /><font face="Times
NewRoman" size="2">pls guide /help me to solve the above problem . thanks in advance.</font><br /><br /><br /><br
/><tablewidth="100%"><tr valign="top"><td></td><td><div align="center"><font face="sans-serif" size="2"><b>Tomasz Myrta
<jasiek@postgresql.org></b></font></div><divalign="center"><br /><font face="sans-serif" size="2">Sent by:
pgsql-sql-owner@postgresql.org</font></div><divalign="center"><p><font face="sans-serif" size="2">01/27/2004 10:56 AM
CET</font></div><divalign="center"><br /></div></td><td><font face="sans-serif" size="2"> To:
azwa@nc.com.my</font><br/><font face="sans-serif" size="2"> cc: pgsql-sql@postgresql.org</font><br
/><fontface="sans-serif" size="2"> Subject: Re: [SQL] time series data</font></td></tr></table><br /><br
/><fontface="Courier New" size="2"><br /> Dnia 2004-01-27 02:42, Użytkownik azwa@nc.com.my napisał:<br /> > <br />
><br /> > Hi,<br /> > <br /> > thanks for the info..btw can u pls explain a little bit detail since <br
/>> i can't get thru yr solution.<br /> > thanks in advance<br /><br /> insert into time_table
(time_key,year_id,month_id,month_desc,day_id)<br/> select<br /> newid,<br /> extract('year' from your_date),<br />
extract('month' from your_date),<br /> to_char(your_date,'mon'),<br /> extract('day' from your_date)<br />
...<br/> from (select nextval('time_seq') as newid,<br /> '1994-01-01'::date+'1 day'::interval * currval('time_seq') as
your_date)x;<br /><br /><br /> Everything you need is to execute query above as many times as you need.<br /><br />
Regards,<br/> Tomasz Myrta<br /><br /> ---------------------------(end of broadcast)---------------------------<br />
TIP7: don't forget to increase your free space map settings<br /></font><br />