pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFormat intervall as hours/minutes etc
6+ messages / 4 participants
[nested] [flat]
* Format intervall as hours/minutes etc
@ 2007-09-16 11:00 Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 11:14 ` Re: Format intervall as hours/minutes etc Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Joseph Krogh @ 2007-09-16 11:00 UTC (permalink / raw)
To: pgsql-sql
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Format intervall as hours/minutes etc
2007-09-16 11:00 Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
@ 2007-09-16 11:14 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-09-16 11:27 ` Re: Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Kretschmer @ 2007-09-16 11:14 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Format intervall as hours/minutes etc
2007-09-16 11:00 Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 11:14 ` Re: Format intervall as hours/minutes etc Andreas Kretschmer <akretschmer@spamfence.net>
@ 2007-09-16 11:27 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 15:41 ` Re: Format intervall as hours/minutes etc Tom Lane <tgl@sss.pgh.pa.us>
2007-09-16 16:45 ` Re: Format intervall as hours/minutes etc Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 2 replies; 6+ messages in thread
From: Andreas Joseph Krogh @ 2007-09-16 11:27 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Format intervall as hours/minutes etc
2007-09-16 11:00 Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 11:14 ` Re: Format intervall as hours/minutes etc Andreas Kretschmer <akretschmer@spamfence.net>
2007-09-16 11:27 ` Re: Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
@ 2007-09-16 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-16 19:32 ` Re: Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-09-16 15:41 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql@postgresql.org, Andreas Kretschmer <akretschmer@spamfence.net>
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
inline patch 1: timestamp.c (+2/-2 lines) download inline-1.patch
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
{
* Re: Format intervall as hours/minutes etc
2007-09-16 11:00 Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 11:14 ` Re: Format intervall as hours/minutes etc Andreas Kretschmer <akretschmer@spamfence.net>
2007-09-16 11:27 ` Re: Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 15:41 ` Re: Format intervall as hours/minutes etc Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-09-16 19:32 ` Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 6+ messages in thread
From: Andreas Joseph Krogh @ 2007-09-16 19:32 UTC (permalink / raw)
To: pgsql-sql
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Format intervall as hours/minutes etc
2007-09-16 11:00 Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 11:14 ` Re: Format intervall as hours/minutes etc Andreas Kretschmer <akretschmer@spamfence.net>
2007-09-16 11:27 ` Re: Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
@ 2007-09-16 16:45 ` Shane Ambler <pgsql@Sheeky.Biz>
1 sibling, 0 replies; 6+ messages in thread
From: Shane Ambler @ 2007-09-16 16:45 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql@postgresql.org, Andreas Kretschmer <akretschmer@spamfence.net>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-09-16 19:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-16 11:00 Format intervall as hours/minutes etc Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 11:14 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-09-16 11:27 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-16 19:32 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-09-16 16:45 ` Shane Ambler <pgsql@Sheeky.Biz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox