Re: Converting seconds past midnight to a time - Mailing list pgsql-general

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