Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA34580 for ; Mon, 30 Aug 1999 09:05:40 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id PAA25969; Mon, 30 Aug 1999 15:05:27 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 11LSsy-0001om-00; Mon, 30 Aug 1999 14:58:16 +0000 Message-ID: <37CA814B.89F6AB51@sferacarta.com> Date: Mon, 30 Aug 1999 15:04:12 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Frank Joerdens CC: pgsql-sql@postgresql.org Subject: Re: [SQL] datetime query issue References: <19990828153506.A18896@shark.x9media.com> <19990828204148.B18896@shark.x9media.com> Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Try this: select extract(year from date) from tablename; select extract(month from date) from tablename; select extract(day from date) from tablename; select extract(hour from date) from tablename; select extract(minute from date) from tablename; select extract(minute from date) from tablename; select extract(second from date) from tablename; José Frank Joerdens ha scritto: > 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 > > > > > > ************ > > ************