Обсуждение: 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
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
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
azwa@postgresql.org Sent by: pgsql-sql-owner@postgresql.org 01/21/2004 04:53 PM | To: pgsql-sql@postgresql.org cc: Subject: [SQL] time series data |
Hi,
i want to create time table & the structure as below :
| Column | Type | Modifiers |
| time_key yr_id month_id month_desc day_id | integer integer integer text 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 |
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
and day_id =1 -->31 for each month. So is there any solution (using function, or else) that can give me the above data
in Postgresql??.
thanks in advance.
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
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)
pls guide /help me to solve the above problem . thanks in advance.
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
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