Hour difference?

[Bjørn T Johansen]

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
bt*@havleik.no Havleik Consulting
Phone : [phone] Conradisvei 4
Fax : [phone] N-1338 Sandvika
Cellular : [phone] 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 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruno Wolff III]

On Mon, Aug 18, 2003 at 16:09:43 +0200,
Bjørn T Johansen <bt*@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

[Bruno Wolff III]

On Mon, Aug 18, 2003 at 17:56:00 +0200,
Bjørn T Johansen <bt*@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.

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

http://archives.postgresql.org

[Bjørn T Johansen]

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 <bt*@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
bt*@havleik.no Havleik Consulting
Phone : [phone] Conradisvei 4
Fax : [phone] N-1338 Sandvika
Cellular : [phone] 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 YourEmailAddressHere" to ma*******@postgresql.org)

[Bruno Wolff III]

On Mon, Aug 18, 2003 at 11:19:35 -0700,
Jonathan Bartlett <jo*****@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.

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

[Jonathan Bartlett]

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 <bt*@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

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

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

[Bjørn T Johansen]

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

---------------------------(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