Re: temporal variants of generate_series()

From: "JEAN-PIERRE PELLETIER" <pelletier_32(at)sympatico(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: temporal variants of generate_series()
Date: 2007-05-02 19:24:42
Message-ID: BAY133-F22FB4C01C9185AD384E4D895420@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Here's a shorter version:

On the date variant, I wasn't sure how to handle intervals with parts
smaller than days:
floor, ceiling, round or error out
To get round, the last parameters of generate_series would be
extract('epoch' FROM '1 day'::interval)::bigint * round(extract('epoch' FROM
$3) / extract('epoch' FROM '1 day'::interval))::bigint

CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
'epoch'::timestamptz + s.i * '1 second'::interval AS "generate_series"
FROM
generate_series(
extract('epoch' FROM $1)::bigint,
extract('epoch' FROM $2)::bigint,
extract('epoch' FROM $3)::bigint
) s(i);
$$;

CREATE OR REPLACE FUNCTION generate_series (
start_ts date,
end_ts date,
step interval
) RETURNS SETOF date
STRICT
LANGUAGE sql
AS $$
SELECT
('epoch'::date + s.i * '1 second'::interval)::date AS "generate_series"
FROM
generate_series(
extract('epoch' FROM $1)::bigint,
extract('epoch' FROM $2)::bigint,
extract('epoch' FROM date_trunc('day', $3))::bigint -- does a floor
) s(i);
$$;

Jean-Pierre Pelletier
e-djuster

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2007-05-02 19:58:26 Re: Sequential scans
Previous Message Andrew Dunstan 2007-05-02 19:04:04 Re: Feature freeze progress report