Re: [SQL] Mail Authentification
Norman Virus Control a supprimé le message original qui contenait le virus [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Group by 15 Minute Steps
Hi,
does anyone hava an idea how to group data e.g by 15 minute steps?
I have the following data in my "data_diff" table
[...]
snmp=# \d data_diff
Table "public.data_diff"
Column | Type | Modifiers
-+-+---
id | integer | not null default
nextval('public.data_diff_id_seq'::text)
device | integer |
psqlzeit| integer |
snmpzeit| integer |
ifinoctets | bigint |
ifoutoctets | bigint |
Indexes:
"data_diff_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"data_diff_device_fkey" FOREIGN KEY (device) REFERENCES device(id)
ON UPDATE CASCADE ON DELETE CASCADE
[...]
An example query looks like this:
snmp=# select snmpzeit as snmp,ifinoctets,ifoutoctets from data_diff
where device=5 order by psqlzeit desc;
snmp| ifinoctets | ifoutoctets
++-
268704 | 111382 | 280566
268405 | 78874 | 22592
268104 | 88645 | 32803
267804 | 76273 | 19024
...
255204 | 149963 | 62889
254904 | 164788 | 81594
254604 | 147720 | 62093
254305 | 161958 | 78813
254005 | 155495 | 68143
Usally, the steps between each entries is 300 seconds.
How can I group by by 15, 30, 45 minutes so that i can get averages over
the specified timeframe?
Greetings,
Martin
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] date subtraction
I need to do date calculation similar to oracle in postgres. like sysdate - creation_date of the record which returns no of days in oracle which you can convert to hours or second by multiplying by 60 or 3600. How do i achieve this in postgres. Thanks Ashok ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] tablename as function parameter
I'm trying to create a function (language 'sql') with table name as single input parameter, but I always get syntax errors at "$1". Probably I am use wrong types. simple example: create function testfct (text) language sql as 'select count(*) from $1' fails. I tested type alternatives to 'text' , such as 'name' or 'sql_identifier' - no success. What is the right type ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL]
Sorry, I tried to make my subject as good as possible. I have a table where I store the dates in which I take out of my inventory (like "installation dates") table --- row_id SERIAL date DATE fk_item INTEGER and that's pretty much it. I want to have a query returning how long have been certain items lasting. Say I have: SELCT date FROM table WHERE fk_item = "INKJET_INK-BW" date - 2005-02-02 2005-03-05 2005-04-07 2005-05-02 I need something to calculate the intervals between those dates, like this: intervals (in days) 31 34 25 So I can get the stddev and stuff from the "duration" of the items. I've been having a hard time with it. I'm trying NOT to program new functions. Any help will be appreciated. --Octavio -- Ing. Octavio Alvarez Piza, Jefe de Informática, TBC Universidad. Tel.: +52 (664) 621-7111 ext. 133; E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Group by 15 Minute Steps
# Usally, the steps between each entries is 300 seconds. How can I # group by by 15, 30, 45 minutes so that i can get averages over the # specified timeframe? For 15-minute data, I'd compute the "quadrant" of each record and group by the quadrant number. Anything that occurs from :00 to :14 is in quadrant zero, :15 - :29 is quadrant 1, etc., yielding quadrants 0-3. mydb> SELECT (time / (15 * 60)) AS quadrant, SUM(ifinoctets) FROM mytable GROUP BY quadrant ORDER BY quadrant; Divide the time by 60 to get minutes and 15 to get quadrants. You can see how to extend this for other intervals. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] query
Hi, In oracle we write sysdate-1 For example,we write a query (select * from table1 where created_date>=sysdate-1).Whats its equivalent in postgre? Chandan
[SQL] timestamp precision - can I control precision at select time or set for all time?
I have a database with several tables that use timestamp without time zone type. I upgraded from an older pgsql and have code that does not expect the precision in the select output. Is there a way I can get the effect of zero precision without modifying all the tables? The docs say it usees "default precision" as set in the "timestamp literal". I wasn't able to find docs describing what literals are. If there's a way I can set this to zero precision for everything, that'd save me a bunch of time. with thanks, Royce ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] tablename as function parameter
# I'm trying to create a function (language 'sql') with table name as
# single input parameter, but I always get syntax errors at
# "$1". Probably I am use wrong types.
#
# simple example:
#
# create function testfct (text) language sql as 'select count(*) from
# $1'
This doesn't work because the value of $1 isn't literally substituted
into the SQL function that you've created; it's treated as a value
token that can only be used in certain contexts.
If you want to select records from an arbitrary table, you can use the
table name parameter to build and execute a cursor in plpgsql. In my
experience, return values can't be quite so polymorphic without a lot
of pain and suffering but, then again, using SELECT * FROM $table
inside a function and expecting to return all of the results is
probably not something you'll need very often.
But here's a way, to be didactic:
CREATE OR REPLACE FUNCTION test(text) RETURNS SETOF record AS '
DECLARE
_table ALIAS FOR $1;
_mycursor refcursor;
_row record;
BEGIN
OPEN _mycursor FOR EXECUTE ''SELECT * FROM '' || _table;
FETCH _mycursor INTO _row;
WHILE FOUND LOOP
RETURN NEXT _row;
FETCH _mycursor INTO _row;
END LOOP;
RETURN;
END
' LANGUAGE plpgsql;
If you return SETOF RECORD, you'll need to be explicit about how the
return value is treated, depending on what you expect to get back from
the function:
mydb> SELECT * FROM test('mytable') AS (col1 integer, col2 text, col3
date);
--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] equivalent of oracle rank() in postgres
Hi, Jus wanted the equivalent for rank() as in tis example.. SELECT * FROM ( SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) EMPRANK FROM employees) WHERE emprank = 3; Rgds, Chandan
