Hour difference?

On Mon, Aug 18, 2003 at 16:09:43 +0200,
Bj�rn T Johansen <btj@havleik.no> wrote:

I need to compute the difference of Time fields, in the format HHMM. Is
it possible to do the math in the Select?

Despite what it says in the documentation, you can't use that format
for the type time.
If timestamps will work for you, you can use to_timestamp to convert
to a timestamps and then subtract them to get an interval.
Another option would be to massage the strings to use a : separator
between the hours and minutes fields and then cast the strings to times.

I am already using Time for time fields (i.e. timestamp fields without
the date part) in my database, are you saying this doesn't work???

BTJ

On Mon, 2003-08-18 at 17:55, Bruno Wolff III wrote:

On Mon, Aug 18, 2003 at 16:09:43 +0200,
Bj�rn T Johansen <btj@havleik.no> wrote:

I need to compute the difference of Time fields, in the format HHMM. Is
it possible to do the math in the Select?

Despite what it says in the documentation, you can't use that format
for the type time.
If timestamps will work for you, you can use to_timestamp to convert
to a timestamps and then subtract them to get an interval.
Another option would be to massage the strings to use a : separator
between the hours and minutes fields and then cast the strings to times.

--
-----------------------------------------------------------------------------------------------
Bj�rn T Johansen (BSc,MNIF)
Executive Manager
btj@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------

On Mon, Aug 18, 2003 at 17:56:00 +0200,
Bj�rn T Johansen <btj@havleik.no> wrote:

I am already using Time for time fields (i.e. timestamp fields without
the date part) in my database, are you saying this doesn't work???

No. You can't use HHMM format for input without doing some more work.
You can use HH:MM as an input format.

If you already have the data loaded into time fields, you can just
subtract them to get an interval.

Is there a way to get an interval in a standard format? It seems like it
keeps changing it's ouput style based on the time length.

Jon

On Mon, 18 Aug 2003, Bruno Wolff III wrote:

fingerless=# select '7:43'::time AS start, '12:17'::time AS end,
(('12:17'::time) - ('7:43'::time))::interval AS difference;
start | end | difference
----------+----------+------------
07:43:00 | 12:17:00 | 04:34
(1 row)

Hope that helps.. Just subsitute your column names for the times.

--Steve

Bj�rn T Johansen wrote:

On Mon, Aug 18, 2003 at 11:19:35 -0700,
Jonathan Bartlett <johnnyb@eskimo.com> wrote:

Is there a way to get an interval in a standard format? It seems like it
keeps changing it's ouput style based on the time length.

Extracting epoch from an interval will return the length in seconds.
(With months treated as having 30 days and years as having 12 months.
But that shouldn't matter for your application.) You can then make
calculations with that number to produce whatever output format you want.

Well, that might help, thanks... :)

BTJ