Thread: time series data
<br /><br /><font face="Times New Roman" size="2">Hi,</font><br /><br /><br /><font face="Times New Roman" size="2">
iwant to create time table & the structure as below :</font><br /><br /><font face="Times New Roman" size="2">
</font><table border="border"><tr valign="top"><td><font face="sans-serif"
size="2">Column</font></td><td><fontface="sans-serif" size="2"> Type</font></td><td><font face="sans-serif"
size="2"> Modifiers</font></td></tr><trvalign="top"><td><font face="sans-serif" size="2">time_key</font><br /><font
face="sans-serif"size="2">yr_id</font><br /><font face="sans-serif" size="2">month_id</font><br /><font
face="sans-serif"size="2">month_desc</font><br /><font face="sans-serif" size="2">day_id</font></td><td><font
face="sans-serif"size="2"> integer</font><br /><font face="sans-serif" size="2"> integer</font><br /><font
face="sans-serif"size="2"> integer</font><br /><font face="sans-serif" size="2"> text</font><br /><font
face="sans-serif"size="2"> integer</font></td><td><font face="sans-serif" size="2"> not null default
nextval('time_seq'::text)</font></td></tr></table><br/><br /><br /><font face="Times New Roman" size="2">Example of
datain time table:</font><br /><table border="border"><tr valign="top"><td><font face="Times New Roman"
size="2">Time_key</font></td><td><fontface="Times New Roman" size="2"> yr_id</font></td><td><font face="Times New
Roman"size="2"> month_id</font></td><td><font face="Times New Roman" size="2"> month_desc</font></td><td><font
face="TimesNew Roman" size="2"> day_id</font></td></tr><tr valign="top"><td></td><td><font face="Times New Roman"
size="2"> 1999</font></td><td><fontface="Times New Roman" size="2"> 1</font></td><td><font face="Times New Roman"
size="2"> Jan</font></td><td><font face="Times New Roman" size="2"> 1</font></td></tr><tr
valign="top"><td></td><td><fontface="Times New Roman" size="2"> 1999</font></td><td><font face="Times New Roman"
size="2"> 1</font></td><td><font face="Times New Roman" size="2"> Jan</font></td><td><font face="Times New Roman"
size="2"> 2</font></td></tr><tr valign="top"><td></td><td><font face="Times New Roman"
size="2"> 2000</font></td><td><fontface="Times New Roman" size="2"> 1</font></td><td><font face="Times New Roman"
size="2"> Jan</font></td><td><font face="Times New Roman" size="2"> 1</font></td></tr></table><br /><br /><br /><font
face="TimesNew Roman" size="2">time_key data will keep increment reading from sequence. i'm going to have a data from
yr_id=1994 --> 2009 , month_id =1 -->12 </font><br /><font face="Times New Roman" size="2">and day_id =1 -->31
foreach month. So is there any solution (using function, or else) that can give me the above data</font><br /><font
face="TimesNew Roman" size="2"> in Postgresql??. </font><br /><br /><font face="Times New Roman" size="2"> thanks in
advance.</font>
Dnia 2004-01-21 09:53, Użytkownik azwa@nc.com.my napisał:
> time_key integer not null default nextval('time_seq'::text)> Example of data in time table:> Time_key
yr_id month_id month_desc day_id> 1999 1 Jan 1> 1999 1 Jan
2> 2000 1 Jan 1
Use this integer sequence and interval datatype to get date result:
your_date='1994-01-01'::date+'1 day'::integer * time_key
Now you can do whatever you want with this date - look at Postgresql
documentation "6.8. Date/Time Functions and Operators" -> "extract"
Regards,
Tomasz Myrta
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał: > Use this integer sequence and interval datatype to get date result: > your_date='1994-01-01'::date+'1 day'::integer * time_key ^^^^^^^ Sorry, use interval here. Regards, Tomasz Myrta
<br /><br /><font face="Courier New" size="2">Hi,</font><br /><br /><font face="Courier New" size="2"> thanks for the
info..btwcan u pls explain a little bit detail since i can't get thru yr solution.</font><br /><font face="Courier New"
size="2">thanksin advance</font><br /><br /><br /><font face="Courier New" size="2">Dnia 2004-01-21 10:37, Użytkownik
TomaszMyrta napisał:<br /><br /> > Use this integer sequence and interval datatype to get date result:<br /> >
your_date='1994-01-01'::date+'1day'::integer * time_key<br />
^^^^^^^<br /> Sorry, use interval here.<br /><br /> Regards,<br /> Tomasz Myrta</font><br /><br /><br
/><tablewidth="100%"><tr valign="top"><td></td><td><div align="center"><font face="sans-serif"
size="2"><b>azwa@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/21/2004 04:53
PM</font></div><divalign="center"><br /></div></td><td><font face="sans-serif" size="2"> To:
pgsql-sql@postgresql.org</font><br/><font face="sans-serif" size="2"> cc: </font><br /><font
face="sans-serif"size="2"> Subject: [SQL] time series data</font></td></tr></table><br /><br /><font
face="TimesNew Roman"><br /></font><font face="Times New Roman" size="2"><br /> Hi,</font><font face="Times New
Roman"><br/><br /></font><font face="Times New Roman" size="2"><br /> i want to create time table & the
structureas below :</font><font face="Times New Roman"><br /></font><font face="Times New Roman" size="2"><br />
</font><font face="Times New Roman"> </font><table border="4"><tr valign="top"><td><font face="sans-serif"
size="2">Column</font><fontface="Times New Roman"> </font></td><td><font face="sans-serif" size="2"> Type</font><font
face="TimesNew Roman"> </font></td><td><font face="sans-serif" size="2"> Modifiers</font><font face="Times New Roman">
</font></td></tr><trvalign="top"><td><font face="sans-serif" size="2">time_key</font><font face="Times New Roman">
</font><fontface="sans-serif" size="2"><br /> yr_id</font><font face="Times New Roman"> </font><font face="sans-serif"
size="2"><br/> month_id</font><font face="Times New Roman"> </font><font face="sans-serif" size="2"><br />
month_desc</font><fontface="Times New Roman"> </font><font face="sans-serif" size="2"><br /> day_id</font><font
face="TimesNew Roman"> </font></td><td><font face="sans-serif" size="2"> integer</font><font face="Times New Roman">
</font><fontface="sans-serif" size="2"><br /> integer</font><font face="Times New Roman"> </font><font
face="sans-serif"size="2"><br /> integer</font><font face="Times New Roman"> </font><font face="sans-serif"
size="2"><br/> text</font><font face="Times New Roman"> </font><font face="sans-serif" size="2"><br />
integer</font><fontface="Times New Roman"> </font></td><td><font face="sans-serif" size="2"> not null default
nextval('time_seq'::text)</font></td></tr></table><br/><font face="Times New Roman"><br /><br /></font><font
face="TimesNew Roman" size="2"><br /> Example of data in time table:</font><font face="Times New Roman"> </font><table
border="4"><trvalign="top"><td><font face="Times New Roman" size="2">Time_key</font><font face="Times New Roman">
</font></td><td><fontface="Times New Roman" size="2"> yr_id</font><font face="Times New Roman"> </font></td><td><font
face="TimesNew Roman" size="2"> month_id</font><font face="Times New Roman"> </font></td><td><font face="Times New
Roman"size="2"> month_desc</font><font face="Times New Roman"> </font></td><td><font face="Times New Roman" size="2">
day_id</font><fontface="Times New Roman"> </font></td></tr><tr valign="top"><td></td><td><font face="Times New Roman"
size="2"> 1999</font><fontface="Times New Roman"> </font></td><td><font face="Times New Roman" size="2"> 1</font><font
face="TimesNew Roman"> </font></td><td><font face="Times New Roman" size="2"> Jan</font><font face="Times New Roman">
</font></td><td><fontface="Times New Roman" size="2"> 1</font><font face="Times New Roman"> </font></td></tr><tr
valign="top"><td></td><td><fontface="Times New Roman" size="2"> 1999</font><font face="Times New Roman">
</font></td><td><fontface="Times New Roman" size="2"> 1</font><font face="Times New Roman"> </font></td><td><font
face="TimesNew Roman" size="2"> Jan</font><font face="Times New Roman"> </font></td><td><font face="Times New Roman"
size="2"> 2</font><font face="Times New Roman"> </font></td></tr><tr valign="top"><td></td><td><font face="Times New
Roman"size="2"> 2000</font><font face="Times New Roman"> </font></td><td><font face="Times New Roman" size="2">
1</font><fontface="Times New Roman"> </font></td><td><font face="Times New Roman" size="2"> Jan</font><font
face="TimesNew Roman"> </font></td><td><font face="Times New Roman" size="2"> 1</font></td></tr></table><br /><font
face="TimesNew Roman"><br /><br /></font><font face="Times New Roman" size="2"><br /> time_key data will keep increment
readingfrom sequence. i'm going to have a data from yr_id =1994 --> 2009 , month_id =1 -->12 <br /> and day_id =1
-->31for each month. So is there any solution (using function, or else) that can give me the above data</font><font
face="TimesNew Roman"> </font><font face="Times New Roman" size="2"><br /> in Postgresql??. </font><font face="Times
NewRoman"><br /></font><font face="Times New Roman" size="2"><br /> thanks in advance.</font><br />
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
Using Postgresql's source files I compiled postgresql 7.4.1 on a dedicated machine with a customized Redhat 7.2 system. Postgresql often stops after a time (unknown, changing). Below is the log file just after Postgresql database server is started manually. Log can't record the cause of this problem. _____________________ LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was interrupted at 2004-01-27 03:18:09 CST LOG: checkpoint record is at 0/4470A6C LOG: redo record is at 0/4470A6C; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 1275; next OID: 234555 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/4470AAC LOG: redo is not required LOG: database system is ready ______________________________________ Please note that I don't experience such a problem on a Slackware 9.0 system. Cavit Keskin cavit@binbir.net
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, On Tue, 27 Jan 2004, Cavit Keskin wrote: > Using Postgresql's source files I compiled postgresql 7.4.1 on a > dedicated machine with a customized Redhat 7.2 system. > Postgresql often stops after a time (unknown, changing). > Below is the log file just after Postgresql database server is started > manually. Log can't record the cause of this problem. <snip> Could be something with hardware or power failure? Also, could you please increase logging level (log_min_messages in postgresql.conf)? Regards, - -- Devrim GUNDUZ devrim@gunduz.org devrim.gunduz@linux.org.tr http://www.TDMSoft.com http://www.gunduz.org -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFAFlGntl86P3SPfQ4RAsRMAKDnFnGylSZJc4rgmGv8FZWaVFhg1gCdE1n4 Z6BPzsEXBcYkJV1UIQPiiYY= =uK9h -----END PGP SIGNATURE-----
"Cavit Keskin" <cavit@binbir.net> writes:
> Postgresql often stops after a time (unknown, changing).
You haven't given any information that would allow anyone to help :-(
> Below is the log file just after Postgresql database server is started
> manually. Log can't record the cause of this problem.
We need to see the log entries from before the crash, not after.
regards, tom lane
<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 />