On 12/19/2005 04:11:09 PM, Jim C. Nasby wrote:
>
> Another option would be creating a set of timestamp math functions;
> that
> would probably help cut down on the number of questions about this.
I solved it by converting to numeric. Here's my functions. (I haven't
tested the spm (seconds past midnight) ones at all yet. Just
wrote em.) They don't help with timestamps, just dates and times.
But something similar would work for timestamps.
(Sorry about the GPL. If Postgres is really interested in
using this code ask and I'll relicense it for the project.)
--
-- General purpose date functions.
--
CREATE OR REPLACE FUNCTION julian(this_date DATE)
RETURNS INT
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a date into its Julian Day.
--
-- Copyright (C) 2004 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- $Id: julian.m4,v 1.3 2004/09/13 20:42:47 kop Exp $
--
-- Syntax: julian(date)
--
-- Input:
-- date The date to convert.
--
-- Returns:
-- The Julian Day of the date.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
-- *wrong value!* day_zero CONSTANT DATE := TO_DATE('0', 'J');
day_zero CONSTANT DATE
:= CURRENT_DATE - CAST (to_char(CURRENT_DATE, 'J') AS
INT);
BEGIN
RETURN this_date - day_zero;
END;
$$;
CREATE OR REPLACE FUNCTION julian_to(julian_day INT)
RETURNS DATE
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a Julian Day to its corresponding date.
--
-- Copyright (C) 2004 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- $Id: julian.m4,v 1.3 2004/09/13 20:42:47 kop Exp $
--
-- Syntax: julian_to(julian_day)
--
-- Input:
-- julian_date The date to convert.
--
-- Returns:
-- The date value for the given Julian Day.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
day_zero CONSTANT DATE
:= CURRENT_DATE - CAST (to_char(CURRENT_DATE, 'J') AS
INT);
BEGIN
RETURN day_zero + julian_day;
END;
$$;
--
-- Babase Seconds Past Midnight Postgresql functions.
--
--
-- General purpose time functions.
--
--
-- To Seconds Past Midnight
--
CREATE OR REPLACE FUNCTION spm(this_time TIME)
RETURNS DOUBLE PRECISION
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a time into a number of seconds past midnight.
--
-- Copyright (C) 2005 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- Syntax: spm(time)
--
-- Input:
-- time The time to convert.
--
-- Returns:
-- The number of seconds past midnight of the time.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
BEGIN
RETURN EXTRACT(EPOCH FROM this_time);
END;
$$;
CREATE OR REPLACE FUNCTION spm(this_interval INTERVAL)
RETURNS DOUBLE PRECISION
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert an interval into a number of seconds past midnight.
--
-- Copyright (C) 2005 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- Syntax: spm(interval)
--
-- Input:
-- interval The interval to convert.
--
-- Returns:
-- The number of seconds in the interval modulo the number
-- of seconds in a day.
--
-- Remarks:
-- Slightly wierd.
DECLARE
BEGIN
RETURN spm(CAST(this_interval AS TIME));
END;
$$;
CREATE OR REPLACE FUNCTION spm(this_timestamp TIMESTAMP)
RETURNS DOUBLE PRECISION
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a timestamp into a number of seconds past midnight.
--
-- Copyright (C) 2005 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- Syntax: spm(timestamp)
--
-- Input:
-- timestamp The timestamp to convert.
--
-- Returns:
-- The number of seconds past midnight of the time portion of the
-- timestamp.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
BEGIN
RETURN spm(CAST(this_timestamp AS TIME));
END;
$$;
CREATE OR REPLACE FUNCTION spm(this_time TIME(0))
RETURNS INT
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a time(0) into a number of seconds past midnight.
--
-- Copyright (C) 2005 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- Syntax: spm(time)
--
-- Input:
-- time The time to convert.
--
-- Returns:
-- The number of seconds past midnight of the time portion of the
-- time.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
BEGIN
RETURN CAST(spm(CAST(this_time AS TIME)) AS INT);
END;
$$;
--
-- From the number of seconds past midnight to a time.
--
CREATE OR REPLACE FUNCTION spm_to(secs DOUBLE PRECISION)
RETURNS TIME
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a number of seconds past midnight to its corresponding
time.
--
-- Copyright (C) 2005 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- Syntax: spm_to(secs)
--
-- Input:
-- secs The number of seconds to convert.
--
-- Returns:
-- The time value for the given number of seconds past midnight.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
BEGIN
RETURN CAST ( secs * CAST ('1 second' AS interval) AS time);
END;
$$;
CREATE OR REPLACE FUNCTION spm_to(secs INT)
RETURNS TIME
LANGUAGE plpgsql
IMMUTABLE
AS $$
-- Convert a number of seconds past midnight to its corresponding
time.
--
-- Copyright (C) 2005 Karl O. Pinc <kop@meme.com>
-- Distributed under the GNU General Public License, version 2 or
later.
--
-- Syntax: spm_to(secs)
--
-- Input:
-- secs The number of seconds to convert.
--
-- Returns:
-- The time value for the given number of seconds past midnight.
--
-- Remarks:
-- Really ought to be built-into postgresql.
DECLARE
BEGIN
RETURN CAST ( secs * CAST ('1 second' AS interval) AS time);
END;
$$;
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
