Re: [SQL] 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
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/21/2004 04:53 PM
To: [EMAIL PROTECTED]
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.
Re: [SQL] time series data
Dnia 2004-01-27 02:42, Użytkownik [EMAIL PROTECTED] 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
[SQL] postgreSQl 7.4.1 and Hibernate
hi, just wanted to know whether anyone on this group uses Hibernate in conjunction with postgreSQL 7.4.1? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] PgSQL Down
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] PgSQL Down
-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. Could be something with hardware or power failure? Also, could you please increase logging level (log_min_messages in postgresql.conf)? Regards, - -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.TDMSoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFAFlGntl86P3SPfQ4RAsRMAKDnFnGylSZJc4rgmGv8FZWaVFhg1gCdE1n4 Z6BPzsEXBcYkJV1UIQPiiYY= =uK9h -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [BUGS] PgSQL Down
"Cavit Keskin" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] postgreSQl 7.4.1 and Hibernate
Beya, > just wanted to know whether anyone on this group uses Hibernate in > conjunction with postgreSQL 7.4.1? Not me personally but there are some companies who do. Also Alzabo. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Query TIME ZONE
Hi All, I have a query regarding Time Zone Interval QUERY: "select current_time AT TIME ZONE INTERVAL '+5:30'" it is running fine BUT When I take the value of interval from DB table like: "select current_time AT TIME ZONE INTERVAL time_difference from customer_events" here : time_difference - is my varchar column in the table customer_events. This gives me errors " parse error at or near "time_difference" at character 43 " I tried to type cast it time_difference::text etc but it in vain. Pls help me ... its realy urgent. Thanks in advance Raman Garg
Re: [SQL] Query TIME ZONE
"Raman" <[EMAIL PROTECTED]> writes: > [ okay: ] > "select current_time AT TIME ZONE INTERVAL '+5:30'" > [ not okay: ] > "select current_time AT TIME ZONE INTERVAL time_difference from customer_events" "TIME ZONE INTERVAL" is not a SQL construct. You have misunderstood the interaction of two different SQL constructs: timestamp AT TIME ZONE timezonespec INTERVAL 'interval-literal' One of the possible forms of "timezonespec" in the AT TIME ZONE operator is an interval value, so your first example works fine. Your second example does not work because the INTERVAL 'foo' construct is only for simple literal constants. > here : time_difference - is my varchar column in the table customer_events. Why are you using varchar rather than an interval column? An interval value would work directly in this construct and would provide some checking that entered values are sane. If you are absolutely intent on using varchar as the column datatype, you can do a run-time cast like this: select current_time AT TIME ZONE "interval"(time_difference) from customer_events but don't complain when the query fails because some rows contain time_difference strings that don't look like legal interval values... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Aggregate function error in 7.4
Hi all. I'm trying to migrate from 7.3.4 to 7.4.1. I've performed a pg_dumpall using the pg_dumpall from 7.4.1. Once 7.4 is running and I try to import the dump, I get the following error: psql:pgsql.dump:301: ERROR: column "r.day" must appear in the GROUP BY clause or be used in an aggregate function The SQL it's choking on is: CREATE VIEW maillog_day_tally AS SELECT day, COALESCE(sum(r.tally), 0) AS received, COALESCE(sum(s.tally), 0) AS sent FROM maillog_recv r FULL JOIN maillog_sent s USING (day, address) GROUP BY day; The tables: CREATE TABLE maillog_sent ( day date NOT NULL, tally integer NOT NULL, address text NOT NULL, CONSTRAINT maillog_sent_pk PRIMARY KEY (day, address) ); CREATE TABLE maillog_recv ( day date NOT NULL, tally integer NOT NULL, address text NOT NULL, CONSTRAINT maillog_recv_pk PRIMARY KEY (day, address) ); Of course if I change the query to read "SELECT r.day ..." it will parse, however, I need to be able to select the day column from the result of the JOIN, not just from one of the two tables. I'm at a loss to explain why 7.4 is treating that SELECT query differently than previous versions. I can't see anything in the 7.4 release notes that would help explain this. Can anyone provide any insight? maillog=> select version(); version - PostgreSQL 7.4.1 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4 .joel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
