Re: time series data
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