Re: [SQL] selecting records X minutes apart

2011-06-13 Thread Gavin Flower 

How about this (that does not require special functions nor triggers:


DROP TABLE IF EXISTS val;

CREATE TABLE val
(
id int,
ts timestamp
);

INSERT INTO val
VALUES
(0, '1-Jan-2010 20:00'),
(1, '1-Jan-2010 20:03'),
(1, '1-Jan-2010 20:04'),
(0, '1-Jan-2010 20:05'),
(1, '1-Jan-2010 20:05'),
(0, '1-Jan-2010 20:08'),
(1, '1-Jan-2010 20:09'),
(0, '1-Jan-2010 20:10');

WITH val_first AS
(
SELECT
id,
min(ts) AS ts
FROM
val
GROUP BY
id
)
SELECT
v.id,
v.ts::time
FROM
val v,
val_first vf
WHERE
v.id = vf.id AND
EXTRACT(EPOCH FROM v.ts - vf.ts)::int % 300 = 0
ORDER BY
id,
ts;


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] extract some column/value pairs via hstore

2011-06-13 Thread Tarlika Elisabeth Schmitz 
In a PL/pgSQL function, I need to extract column/value pairs from a
table ignoring some of the columns.
The excluded column names have previously been stored in an array
"ak", which contains

array['country_fk', 'region_fk', 'id']

SELECT key, value FROM 
(
SELECT (each(hstore(town))).* FROM town 
) AS nk
WHERE key NOT IN (SELECT unnest(ak));

=

The above statement produces the desired result but it seems a bit
convoluted. Can this be done in a more elegant way?

=
PostgreSQL 8.4

example: 
CREATE TABLE town
(
  country_fk character varying(3) NOT NULL,
  region_fk character varying(3) NOT NULL,
  id serial NOT NULL,
  "name" character varying(50) NOT NULL,
  source character varying(2) NOT NULL
)

-- 

Best Regards,
Tarlika Elisabeth Schmitz

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql