Thread: Hour difference?
I need to compute the difference of Time fields, in the format HHMM. Is it possible to do the math in the Select? Regards, BTJ -- ----------------------------------------------------------------------------------------------- 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 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.
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.
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 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.
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:
>I need to compute the difference of Time fields, in the format HHMM. Is
>it possible to do the math in the Select?
>
>
>Regards,
>
>BTJ
>
>
>
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: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Well, that might help, thanks... :)
BTJ
On Mon, 2003-08-18 at 20:47, Steve Worsley 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:
>
> >I need to compute the difference of Time fields, in the format HHMM. Is
> >it possible to do the math in the Select?
> >
> >
> >Regards,
> >
> >BTJ
> >
> >
> >