X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B5E6447637E for ; Sat, 22 Mar 2003 12:47:03 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 4EF2047636C for ; Sat, 22 Mar 2003 12:47:00 -0500 (EST) Received: (qmail 22508 invoked from network); 22 Mar 2003 17:48:10 -0000 Received: from ns1.trade-india.com (HELO www.trade-india.com) (66.234.10.14) by infocom-236-129-del.trade-india.com with SMTP; 22 Mar 2003 17:48:10 -0000 Received: (qmail 18530 invoked by uid 99); 22 Mar 2003 17:47:42 -0000 Received: from 219.65.230.11 (SquirrelMail authenticated user mallah) by mail.trade-india.com with HTTP; Sat, 22 Mar 2003 23:17:42 +0530 (IST) Message-ID: <1081.219.65.230.11.1048355262.squirrel@mail.trade-india.com> Date: Sat, 22 Mar 2003 23:17:42 +0530 (IST) Subject: Re: SQL help for efficient time handling.. From: To: In-Reply-To: <20030322081350.R1246-100000@megazone23.bigpanda.com> References: <200303221332.33121.mallah@trade-india.com> <20030322081350.R1246-100000@megazone23.bigpanda.com> X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal Cc: , X-Mailer: SquirrelMail (version 1.2.6) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-18.1 required=5.0 tests=IN_REP_TO,MISSING_MIMEOLE,MISSING_OUTLOOK_NAME, NO_REAL_NAME,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1,REFERENCES, REPLY_WITH_QUOTES autolearn=ham version=2.50-cvs X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50-cvs (1.172-2003-02-14-exp) X-Archive-Number: 200303/528 X-Sequence-Number: 12567 Hey it Worked!! , tradein_clients=3D# explain analyze select time_id from general.time_dimens= ion where sql_time=3D=20 cast(date_trunc('minute', now()) as time); = QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------------------------- Index Scan = using time_dimension_sql_time_key on time_dimension (cost=3D0.00..3.02 row= s=3D1 width=3D4) (actual time=3D0.06..0.06 rows=3D1 loops=3D1) Index Cond: (sql_time =3D = (date_trunc('minute'::text, now()))::time without time zone) Total runtime: 0.11 msec (3 rows) tradein_clients=3D# > > On Sat, 22 Mar 2003, Rajesh Kumar Mallah wrote: > >> To get current_time_id i use a query like >> SELECT time_id from time_dimension where sql_time=3Ddate_trunc('minute'= , cast(now() as time >> without time zone) ); >> >> it works but uses seq_scan >> Seq Scan on time_dimension (cost=3D0.00..35.00 rows=3D5 width=3D4) (act= ual time=3D4.75..8.16 rows=3D1 >> loops=3D1) >> Filter: ((sql_time)::interval =3D date_trunc('minute'::text, ((now())= ::time without time >> zone)::interval)) >> Total runtime: 8.20 msec > > How about something like: > > select time_id from time_dimension where sql_time=3D > cast(date_trunc('minute', now()) as time); > > > ---------------------------(end of broadcast)--------------------------- = TIP 2: you can get off > all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/