Thread: Format intervall as hours/minutes etc
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 | |
------------------------+---------------------------------------------+
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°
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 | |
------------------------+---------------------------------------------+
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 {
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
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 | | ------------------------+---------------------------------------------+
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.