Обсуждение: Intervals
Is there a way to convert in interval into hours? I have a table that records the amount of time worked by a person and want to sum up all the hours, however with the column being an interval once you reach more than 24 hours it turns that into a day. This is not what I want so instead of outputting 1day 2:00:00 I would want to output 26:00:00 is this possible? Thanks Jake
On Apr 26, 2005, at 18:47, Jake Stride wrote: > Is there a way to convert in interval into hours? I have a table that > records the amount of time worked by a person and want to sum up all > the > hours, however with the column being an interval once you reach more > than 24 hours it turns that into a day. This is not what I want so > instead of outputting 1day 2:00:00 I would want to output 26:00:00 is > this possible? Search the archives from the past couple of days of the pgsql-sql mailing list with the subject line Re: [SQL] to_char(interval) ? and you should find a solution. The archives can be found at http://archives.postgresql.org Hope this helps. Michael Glaesemann grzm myrealbox com
On 4/26/05, Jake Stride <nsuk@users.sourceforge.net> wrote:
> Is there a way to convert in interval into hours? I have a table that
> records the amount of time worked by a person and want to sum up all the
> hours, however with the column being an interval once you reach more
> than 24 hours it turns that into a day. This is not what I want so
> instead of outputting 1day 2:00:00 I would want to output 26:00:00 is
> this possible?
Something along lines of select extract(epoch from '1 day 2
hours'::interval) / 3600
maybe?
Regards,
Dawid
I had the same problem and wrote a small function create function hours(timestamp without time zone, timestamp without time zone) RETURNS integer as $$select cast( (cast($2 as date) - cast($1 as date)) * 24 + extract(hour from cast($2 as time) - cast($1 as time)) as integer)$$ language SQL IMMUTABLE; Andrei Jake Stride wrote: >Is there a way to convert in interval into hours? I have a table that >records the amount of time worked by a person and want to sum up all the >hours, however with the column being an interval once you reach more >than 24 hours it turns that into a day. This is not what I want so >instead of outputting 1day 2:00:00 I would want to output 26:00:00 is >this possible? > >Thanks > >Jake > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005
That is helpful, I have created a slightly different function that returns an interval in the format HH:MM not sure if it will help anyone or anyone has any suggestions to improve it: create function hours(interval) returns varchar as 'SELECT floor(extract(epoch from $1)/3600) || \':\' || (cast(extract(epoch FROM $1) AS integer)%3600)/60;' language SQL IMMUTABLE; Thanks Jake Andrei Gaspar wrote: > I had the same problem and wrote a small function > > create function hours(timestamp without time zone, timestamp without > time zone) RETURNS integer as > $$select cast( (cast($2 as date) - cast($1 as date)) * 24 + > extract(hour from cast($2 as time) - cast($1 as time)) as integer)$$ > language SQL IMMUTABLE; > > Andrei > > Jake Stride wrote: > >> Is there a way to convert in interval into hours? I have a table that >> records the amount of time worked by a person and want to sum up all the >> hours, however with the column being an interval once you reach more >> than 24 hours it turns that into a day. This is not what I want so >> instead of outputting 1day 2:00:00 I would want to output 26:00:00 is >> this possible? >> >> Thanks >> >> Jake >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 8: explain analyze is your friend >> >> >> >> > > -- Jake Stride Senokian Solutions Ltd The TechnoCentre Coventry University Technology Park Puma Way Coventry CV1 2TT T: 0870 744 2030 F: 0870 460 2623 M: 07713 627 304 E: jake.stride@senokian.com
Hi all, I'll really appreciate any help to reduce the disk usage of postgresql. I have a web site witch is data are refreshed each night. Right now the disk usage is about 400 Megs but since I reload data all nights it getting huge. I do vacuum each time I am finished loading data. I look into the db to found where space are used .. here is what I got SELECT relname, (relpages*8)/1024 FROM pg_class ORDER BY relpages DESC; relname ?column? il_idx_1 155 il 83 vd_pk 67 il_pk 61 ic 2 ic_pk 0 iq 0 I see that index on IL (table with 4 column and ~1500000 rows) take about 155 megs. Is that normal that IL has 83 megs but the index 155 ? Vd_pk take about 67 megs but it the table is currently empty. Is that means vacuum don`t shrinks indexes? I tried a reindex with pgadminIII, here is the result. I noticed that only system table were reindexed! Is that normal? NOTICE: table "pg_class" was reindexed NOTICE: table "pg_attrdef" was reindexed NOTICE: table "pg_constraint" was reindexed NOTICE: table "pg_description" was reindexed NOTICE: table "pg_proc" was reindexed NOTICE: table "pg_rewrite" was reindexed NOTICE: table "pg_type" was reindexed NOTICE: table "pg_attribute" was reindexed NOTICE: table "pg_index" was reindexed NOTICE: table "pg_operator" was reindexed NOTICE: table "pg_opclass" was reindexed NOTICE: table "pg_am" was reindexed NOTICE: table "pg_amop" was reindexed NOTICE: table "pg_amproc" was reindexed NOTICE: table "pg_language" was reindexed NOTICE: table "pg_aggregate" was reindexed NOTICE: table "pg_trigger" was reindexed NOTICE: table "pg_cast" was reindexed NOTICE: table "pg_namespace" was reindexed NOTICE: table "pg_conversion" was reindexed NOTICE: table "pg_depend" was reindexed NOTICE: table "pg_statistic" was reindexed NOTICE: table "pg_inherits" was reindexed NOTICE: table "pg_largeobject" was reindexed I look at directory pg_xlog with is 164 megs. I change checkpoint_segments to 1 in postgresql.conf to reduce this. I have 2 questions: -Is there any other stuff I can do to reduce disk space? -What can I do to have à disk usage with is stable? Remember that I flush/load data each night. Thanks for your help /David Total query runtime: 141 ms.