Hi all. Any hint on how to format this interval as number of hour/seconds etc?
select age('2007-09-22 17:00'::timestamp, '2000-02-20 18:00'::timestamp);
age
-------------------------------
7 years 7 mons 1 day 23:00:00

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Andreas Joseph Krogh <andreak@officenet.no> schrieb:

> Hi all. Any hint on how to format this interval as number of hour/seconds etc?
> select age('2007-09-22 17:00'::timestamp, '2000-02-20 18:00'::timestamp);
> age
> -------------------------------
> 7 years 7 mons 1 day 23:00:00

You can use extract(epoch, from ...) like this:

test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract (epoch from '2000-02-20 18:00'::timestamp);
?column?
-----------
239407200
(1 row)

Now you can calculate the hours and so on.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
> Andreas Joseph Krogh <andreak@officenet.no> schrieb:
> > Hi all. Any hint on how to format this interval as number of hour/seconds
> > etc? select age('2007-09-22 17:00'::timestamp, '2000-02-20
> > 18:00'::timestamp); age
> > -------------------------------
> > 7 years 7 mons 1 day 23:00:00
>
> You can use extract(epoch, from ...) like this:
>
> test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract
> (epoch from '2000-02-20 18:00'::timestamp); ?column?
> -----------
> 239407200
> (1 row)
>
> Now you can calculate the hours and so on.

Yes, this works fine for dates >= 1970, but I'm looking for a more general
solution which takes an arbitrary interval as input. The reason why I'm using
PG to calculate this is 'cause it takes 25/23 hour days, leapyears etc. into
account when calculating intervals.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

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

Andreas Joseph Krogh <andreak@officenet.no> writes:
> On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
>> You can use extract(epoch, from ...) like this:
>>
>> test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract
>> (epoch from '2000-02-20 18:00'::timestamp); ?column?
>> -----------
>> 239407200
>> (1 row)
>>
>> Now you can calculate the hours and so on.

> Yes, this works fine for dates >= 1970, but I'm looking for a more general

There's no particular restriction to dates after 1970 there.

> solution which takes an arbitrary interval as input.

Well, you could subtract the two timestamps and then "extract(epoch ...)"
the resulting interval, but I think you'll get the very same answer.

[ pokes at it ... ] Hm, we seem to have an overflow problem in the
interval-to-epoch code for intervals exceeding 60-some years:

regression=# select extract(epoch from '2007-09-22 17:00'::timestamp - '1940-02-20 18:00'::timestamp);
date_part
------------
2132866800
(1 row)

regression=# select extract(epoch from '2007-09-22 17:00'::timestamp - '1930-02-20 18:00'::timestamp);
date_part
-------------
-1846567696
(1 row)

Looks pretty trivial to fix ...

regards, tom lane

Index: timestamp.c
================================================== =================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.181
diff -c -r1.181 timestamp.c
*** timestamp.c 4 Aug 2007 01:26:54 -0000 1.181
--- timestamp.c 16 Sep 2007 15:33:33 -0000
***************
*** 4395,4403 ****
#else
result = interval->time;
#endif
! result += (DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
! result += interval->day * SECS_PER_DAY;
}
else
{
--- 4395,4403 ----
#else
result = interval->time;
#endif
! result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
! result += ((double) SECS_PER_DAY) * interval->day;
}
else
{

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

http://archives.postgresql.org

Andreas Joseph Krogh wrote:
> On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
>> Andreas Joseph Krogh <andreak@officenet.no> schrieb:
>>> Hi all. Any hint on how to format this interval as number of hour/seconds
>>> etc? select age('2007-09-22 17:00'::timestamp, '2000-02-20
>>> 18:00'::timestamp); age
>>> -------------------------------
>>> 7 years 7 mons 1 day 23:00:00
>> You can use extract(epoch, from ...) like this:
>>
>> test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract
>> (epoch from '2000-02-20 18:00'::timestamp); ?column?
>> -----------
>> 239407200
>> (1 row)
>>
>> Now you can calculate the hours and so on.
>
> Yes, this works fine for dates >= 1970, but I'm looking for a more general
> solution which takes an arbitrary interval as input. The reason why I'm using
> PG to calculate this is 'cause it takes 25/23 hour days, leapyears etc. into
> account when calculating intervals.

Is that all you use it for?? ;-)

You may want to add the timezone to get the effect of daylight savings.

postgres=# select age('2007-03-25 7:00:00'::timestamptz, '2007-03-25
1:00:00'::timestamptz);
age
----------
06:00:00
(1 row)

postgres=# select age('2007-03-25 7:00:00+9:30'::timestamptz,
'2007-03-25 1:00:00+9:30'::timestamptz);
age
----------
05:00:00
(1 row)


I haven't used intervals much so I may be missing something.

I get the idea you want the interval to be expressed as 2,765 days and
23 hours or 66,383 hours, which I think would be useful (more so for
shorter intervals).

I am thinking the exact function you are after isn't there - from what I
can find a larger interval is always given as x years y months z days...
which is why extracting the epoch is the easiest point to start your calcs.

Maybe this can be a feature request - functions to give an interval in
total number of days/hours/minutes instead of years months days




--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

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

On Sunday 16 September 2007 17:41:56 Tom Lane wrote:
> Andreas Joseph Krogh <andreak@officenet.no> writes:
> > On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
> >> You can use extract(epoch, from ...) like this:
> >>
> >> test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) -
> >> extract (epoch from '2000-02-20 18:00'::timestamp); ?column?
> >> -----------
> >> 239407200
> >> (1 row)
> >>
> >> Now you can calculate the hours and so on.
> >
> > Yes, this works fine for dates >= 1970, but I'm looking for a more
> > general
>
> There's no particular restriction to dates after 1970 there.
>
> > solution which takes an arbitrary interval as input.
>
> Well, you could subtract the two timestamps and then "extract(epoch ...)"
> the resulting interval, but I think you'll get the very same answer.
>
> [ pokes at it ... ] Hm, we seem to have an overflow problem in the
> interval-to-epoch code for intervals exceeding 60-some years:
>
> regression=# select extract(epoch from '2007-09-22 17:00'::timestamp -
> '1940-02-20 18:00'::timestamp); date_part
> ------------
> 2132866800
> (1 row)
>
> regression=# select extract(epoch from '2007-09-22 17:00'::timestamp -
> '1930-02-20 18:00'::timestamp); date_part
> -------------
> -1846567696
> (1 row)
>
> Looks pretty trivial to fix ...

Ok.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

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

On Mon, 2007-09-17 at 02:15 +0930, Shane Ambler wrote:
> I get the idea you want the interval to be expressed as 2,765 days and
> 23 hours or 66,383 hours, which I think would be useful (more so for
> shorter intervals).
>
> I am thinking the exact function you are after isn't there - from what I
> can find a larger interval is always given as x years y months z days...
> which is why extracting the epoch is the easiest point to start your calcs.
>
> Maybe this can be a feature request - functions to give an interval in
> total number of days/hours/minutes instead of years months days

Doesn't the SQL standard allow one to define intervals as YEAR TO MONTH,
DAY TO HOUR, HOUR TO SECOND and so on? This sets both the greatest unit
to report and the resolution. (YEAR/MONTH cannot be mixed with other
types because of the uncertainty of month lengths.)

Is there any plan to support that?

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings