Received: from shark.x9media.com ([62.104.116.95]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA93381 for ; Sat, 28 Aug 1999 14:26:00 -0400 (EDT) (envelope-from frank@x9media.com) Received: (from frank@localhost) by shark.x9media.com (8.8.7/8.8.7) id UAA19045 for pgsql-sql@postgreSQL.org; Sat, 28 Aug 1999 20:41:49 +0200 Date: Sat, 28 Aug 1999 20:41:49 +0200 From: Frank Joerdens To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] datetime query issue Message-ID: <19990828204148.B18896@shark.x9media.com> References: <19990828153506.A18896@shark.x9media.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.6i In-Reply-To: <19990828153506.A18896@shark.x9media.com>; from Frank Joerdens on Sat, Aug 28, 1999 at 03:35:07PM +0200 um, this was probably unintelligible. this is the table: +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | source_numeric | int8 not null | 8 | | destination_numeric | int8 not null | 8 | | source | text not null | var | | destination | text not null | var | | bytes | int8 not null | 8 | | date | datetime | 8 | +----------------------------------+----------------------------------+-------+ every hour, a set of rows is added to this table with the current timestamp on each row. i guess i need a function to extract the time from datetime. date_trunc may be what i am looking for but i couldn't find information in the documentation as to how to use these functions. On Sat, Aug 28, 1999 at 03:35:07PM +0200, Frank Joerdens wrote: > I'm not sure how to go about this: I want to find all rows with a timestamp > between, say 6 a.m. and 10 p.m. for any number of days. The column format is > datetime. How do I extract the 'time of the day' information from this > column? What I can think of is to create a view for every single day where datetime > is > 6 and < 10 and then another query on all views together but that doesn't sound > very elegant and would be a real memory hog for a large number of days. > > Any better ideas? > > Thanks > > Frank > > > ************