Thread: 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. >> 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. >> -----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? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >>
How about: select abstime(start)::int - abstime(start)::int Cheers, Steve 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? > > http://www.postgresql.org/docs/faqs/FAQ.html