Time handling in pgsql. (fwd)
is there a way taht i can cast that to say "x seconds"
or 26:33:03:00 something like that is much more plesant
to parse than the 1 days format.
It would be great if the 'age' function could be supplied a formatting
string so that you could format the data any way you want.
That's not an easy function to develop, though.
I recently had to write a PHP program which takes a COBOL file and
converts it to pgsql. (Yes, Virginia, there are still COBOL applications
out there!)
To complicate matters, some dates were stored 'yymmdd', some 'yyyymmdd',
some 'mmddyy' and some 'yymm'. The final one needed to be converted into
the last day of the month for pgsql purposes. (I won't get into the
kludges that were done in 1999 for Y2K 'compatibility'.)
Not all those fields had valid data in them, either.
For your purposes, you will probably have to write a function to which
you pass two timestamps. You could get lucky and find out that someone
else has already written one, but I don't see one in the 'contrib'
section of the 7.3 sources. (Are there other user-contributed code
repositories?)
Within that function convert both timestamps into seconds, do
the arithmetic and return the number of seconds.
If you know the range of dates you're likely to use, you may be
able to take a few shortcuts in that.
If you're concerned about leap years, time zones, daylight savings time,
etc, that will complicate matters.
Afterwards, please post your function so the rest of us can use it. :-)
--
Mike Nolan
Actually I'd be happy if i could simply do this
SELECT age(start,end)::seconds From foo;
or something very close to that. Then i could process
the rest in perl.
Jeff.
Show quoted text
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of
nolan@celery.tssi.com
Subject: [GENERAL] Time handling in pgsql. (fwd)
It would be great if the 'age' function could be supplied a formatting
string so that you could format the data any way you want.
Within that function convert both timestamps into seconds, do
the arithmetic and return the number of seconds.
Ok, I found what I wanted.
You can use extract epoch on intervals.
SELECT sum(EXTRACT(EPOCH FROM age(endtime,starttime))) FROM tracking GROUP
BY uwid;
The query above works.
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeff MacDonald
Sent: Tuesday, April 22, 2003 1:20 PM
To: nolan@celery.tssi.com; pgsql general list
Subject: Re: [GENERAL] Time handling in pgsql. (fwd)Actually I'd be happy if i could simply do this
SELECT age(start,end)::seconds From foo;
or something very close to that. Then i could process
the rest in perl.Jeff.
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of
nolan@celery.tssi.com
Subject: [GENERAL] Time handling in pgsql. (fwd)It would be great if the 'age' function could be supplied a formatting
string so that you could format the data any way you want.Within that function convert both timestamps into seconds, do
the arithmetic and return the number of seconds.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
How about: select abstime(start)::int - abstime(start)::int
Cheers,
Steve
Show quoted text
On Tuesday 22 April 2003 10:20 am, Jeff MacDonald wrote:
Actually I'd be happy if i could simply do this
SELECT age(start,end)::seconds From foo;
or something very close to that. Then i could process
the rest in perl.Jeff.
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of
nolan@celery.tssi.com
Subject: [GENERAL] Time handling in pgsql. (fwd)It would be great if the 'age' function could be supplied a formatting
string so that you could format the data any way you want.Within that function convert both timestamps into seconds, do
the arithmetic and return the number of seconds.---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?