Converting seconds past midnight to a time

Started by Karl O. Pincalmost 21 years ago7 messagesgeneralpatches
Jump to latest
#1Michael Glaesemann
grzm@myrealbox.com
In reply to: Karl O. Pinc (#2)
generalpatches
Re: Converting seconds past midnight to a time

On Dec 18, 2005, at 13:25 , Karl O. Pinc wrote:

What is the best way to convert an integer number of
seconds past midnight into a time?

Try your_date::timestamptz + your_seconds * interval '1 sec', e.g.,

test=# select current_date::timestamptz, current_date::timestamptz +
1000 * interval '1 sec';
timestamptz | ?column?
------------------------+------------------------
2005-12-18 00:00:00+09 | 2005-12-18 00:16:40+09
(1 row)

On a related note is there some reason why
interval + int
does not result in the interval plus int number
of seconds?

Why should the int necessarily represent seconds and not some other
amount of time? It's just a unit-less value.

Michael Glaesemann
grzm myrealbox com

#2Karl O. Pinc
kop@meme.com
generalpatches

Hi,

What is the best way to convert an integer number of
seconds past midnight into a time? I can't seem to
figure out a way that does not involve casts to strings,
which seems wasteful.

On a related note is there some reason why
interval + int
does not result in the interval plus int number
of seconds? That would seem the sane approach.
So sane that there must be some reason why it's
not already done.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Karl O. Pinc (#2)
generalpatches
Re: Converting seconds past midnight to a time

"Karl O. Pinc" <kop@meme.com> writes:

What is the best way to convert an integer number of
seconds past midnight into a time?

Intermediate 'interval' value seems to work:

regression=# select (99.44 * '1 second'::interval)::time;
time
-------------
00:01:39.44
(1 row)

On a related note is there some reason why
interval + int
does not result in the interval plus int number
of seconds?

Again, multiplying the number by '1 sec'::interval would get you there.

regards, tom lane

#4Karl O. Pinc
kop@meme.com
In reply to: Michael Glaesemann (#1)
generalpatches
Re: Converting seconds past midnight to a time

On 12/17/2005 10:21:39 PM, Michael Glaesemann wrote:

On Dec 18, 2005, at 13:25 , Karl O. Pinc wrote:

On a related note is there some reason why
interval + int
does not result in the interval plus int number
of seconds?

Why should the int necessarily represent seconds and not some other
amount of time? It's just a unit-less value.

Good question. I guess it's because I couldn't corece
an int into an interval number of seconds. ;-) But if it was to be
anything it should be seconds or milliseconds as those
are the only ones that make the math easy, in the sense of
working with whole numbers.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

#5Jim Nasby
jnasby@pervasive.com
In reply to: Karl O. Pinc (#4)
generalpatches
Re: Converting seconds past midnight to a time

On Sun, Dec 18, 2005 at 07:29:45AM +0000, Karl O. Pinc wrote:

On 12/17/2005 10:21:39 PM, Michael Glaesemann wrote:

On Dec 18, 2005, at 13:25 , Karl O. Pinc wrote:

On a related note is there some reason why
interval + int
does not result in the interval plus int number
of seconds?

Why should the int necessarily represent seconds and not some other
amount of time? It's just a unit-less value.

Good question. I guess it's because I couldn't corece
an int into an interval number of seconds. ;-) But if it was to be
anything it should be seconds or milliseconds as those
are the only ones that make the math easy, in the sense of
working with whole numbers.

Well, generally speaking people work with time either in some native
format (ie: timestamp) or as a number of seconds. I don't think there's
enough consistency between time being a number of milliseconds or
microseconds to warrant that.

Personally, I don't think it would be unreasonable to allow timestamp +
int and timestamp + double (with int and double being treated as
seconds). I don't recall ever seeing an email on the lists from someone
expecting time/timestamp + bare number to mean 'add X hours' or 'add X
fractional seconds', but people do ask about adding X seconds pretty
often.

Another option would be creating a set of timestamp math functions; that
would probably help cut down on the number of questions about this.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

#6Karl O. Pinc
kop@meme.com
In reply to: Jim Nasby (#5)
generalpatches
Re: Converting seconds past midnight to a time

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

#7Bruce Momjian
pgman@candle.pha.pa.us
In reply to: Tom Lane (#3)
generalpatches
Re: [GENERAL] Converting seconds past midnight to a time

Tom Lane wrote:

"Karl O. Pinc" <kop@meme.com> writes:

What is the best way to convert an integer number of
seconds past midnight into a time?

Intermediate 'interval' value seems to work:

regression=# select (99.44 * '1 second'::interval)::time;
time
-------------
00:01:39.44
(1 row)

On a related note is there some reason why
interval + int
does not result in the interval plus int number
of seconds?

Again, multiplying the number by '1 sec'::interval would get you there.

I have applied the following documentation patch to HEAD and 8.1.X.
It is unified diff because those are easier with single-line changes
like this, where the surrounding text isn't as significant.

I also fixed an example '1 day' - '1 hour' which now computes
differently, and added trailing zero seconds, which is our display
default.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Attachments:

/rtmp/difftext/plainDownload+21-15