Hour difference?

**Bruno Wolff III** · Nov 11 '05, 11:25 PM

Re: Hour difference?

On Mon, Aug 18, 2003 at 16:09:43 +0200,
Bjørn T Johansen <btj@havleik.no > wrote:[color=blue]
> I need to compute the difference of Time fields, in the format HHMM. Is
> it possible to do the math in the Select?[/color]

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?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Bruno Wolff III** · Nov 11 '05, 11:25 PM

Re: Hour difference?

On Mon, Aug 18, 2003 at 17:56:00 +0200,
Bjørn T Johansen <btj@havleik.no > wrote:[color=blue]
> 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???[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Bjørn T Johansen** · Nov 11 '05, 11:25 PM

Re: Hour difference?

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:[color=blue]
> On Mon, Aug 18, 2003 at 16:09:43 +0200,
> Bjørn T Johansen <btj@havleik.no > wrote:[color=green]
> > I need to compute the difference of Time fields, in the format HHMM. Is
> > it possible to do the math in the Select?[/color]
>
> 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.[/color]
--
-----------------------------------------------------------------------------------------------
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."
-----------------------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Bruno Wolff III** · Nov 11 '05, 11:25 PM

Re: Hour difference?

On Mon, Aug 18, 2003 at 11:19:35 -0700,
Jonathan Bartlett <johnnyb@eskimo .com> wrote:[color=blue]
> 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.[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Jonathan Bartlett** · Nov 11 '05, 11:25 PM

Re: Hour difference?

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:
[color=blue]
> On Mon, Aug 18, 2003 at 16:09:43 +0200,
> Bjørn T Johansen <btj@havleik.no > wrote:[color=green]
> > I need to compute the difference of Time fields, in the format HHMM. Is
> > it possible to do the math in the Select?[/color]
>
> 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
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Bjørn T Johansen** · Nov 11 '05, 11:25 PM

Re: Hour difference?

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

BTJ

On Mon, 2003-08-18 at 20:47, Steve Worsley wrote:[color=blue]
> 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:
>[color=green]
> >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
> >
> >
> >[/color][/color]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Hour difference?

Hour difference?

Comment

Comment

Comment

Comment

Comment

Comment