Thread: Time difference without intervals
I saw this question on another PG mailing list, and I don't know how to answer. I've tried casting the interval to a timestamp, but that doesn't seem to be supported in 7.2. Does anyone have an answer? Is there any way to make a timestamp difference operation not return an interval? I'd like to get hours, minutes and seconds only, not the "1 day" or whatnot. lbn-dev=# select now() - '2002-11-07 7:42'::timestamp; ?column? -----------------------1 day 02:01:57.987838 (1 row) Thanks, -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + TAGLINE NO ESPELHO ¤ OHLEPSE ON ENILGAT
On Fri, Nov 08, 2002 at 11:20:02 -0700, Roberto Mello <rmello@cc.usu.edu> wrote: > > Is there any way to make a timestamp difference operation not return an > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > or whatnot. When you take a difference of two timestamps, you are going to get an interval that does not have a year, month difference component. You can get the interval value in seconds by using extract(epoch from interval). You can then further manipulate this value to get hours, minutes and seconds.
Roberto Mello <rmello@cc.usu.edu> writes: > Is there any way to make a timestamp difference operation not return an > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > or whatnot. I think your complaint is not that you get an interval, but that you want it displayed differently. See to_char() ... regards, tom lane
On Fri, Nov 08, 2002 at 16:11:05 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Roberto Mello <rmello@cc.usu.edu> writes: > > Is there any way to make a timestamp difference operation not return an > > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > > or whatnot. > > I think your complaint is not that you get an interval, but that you > want it displayed differently. See to_char() ... I looked at to_char before suggesting extract. The problem is that to_char will only return hours modulo days. His question implied that he was going to have intervals longer than a day, so I don't believe to_char will work.
On Fri, Nov 08, 2002 at 04:11:05PM -0500, Tom Lane wrote: > Roberto Mello <rmello@cc.usu.edu> writes: > > Is there any way to make a timestamp difference operation not return an > > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > > or whatnot. > > I think your complaint is not that you get an interval, but that you > want it displayed differently. See to_char() ... It was not a complaint in any way. I apologize if I came across like that. It was an honest question. Thanks for the replies. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "Apple" (c) Copyright 1767, Sir Isaac Newton.
Roberto Mello <rmello@cc.usu.edu> writes: > On Fri, Nov 08, 2002 at 04:11:05PM -0500, Tom Lane wrote: >> I think your complaint is not that you get an interval, but that you >> want it displayed differently. See to_char() ... > It was not a complaint in any way. I apologize if I came across like that. > It was an honest question. Certainly --- I perhaps came across in the wrong way by using the word "complaint". Maybe "gripe" or "concern" would've been better. I was just trying to identify exactly where the problem was. regards, tom lane