X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B2E45475CE5 for ; Sat, 22 Mar 2003 04:11:04 -0500 (EST) Received: from ns.cnlo.ro (cnlo.ro [193.231.112.154]) by postgresql.org (Postfix) with ESMTP id 752A9475B47 for ; Sat, 22 Mar 2003 04:11:01 -0500 (EST) Received: from cnlo.ro (marian.cnlo.ro [192.168.10.64]) by ns.cnlo.ro (8.12.8/8.12.5) with ESMTP id h2M99WnJ023817; Sat, 22 Mar 2003 11:09:32 +0200 Message-ID: <3E7C281A.4050709@cnlo.ro> Date: Sat, 22 Mar 2003 11:08:42 +0200 From: Popeanga Marian User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rajesh Kumar Mallah Cc: pgsql-sql@postgresql.org Subject: Re: SQL help for efficient time handling.. References: <200303221332.33121.mallah@trade-india.com> <3E7C22B8.70601@cnlo.ro> <200303221425.02824.mallah@trade-india.com> Content-Type: multipart/alternative; boundary="------------050104060300000406080604" X-RAVMilter-Version: 8.4.1(snapshot 20020919) (ns.cnlo.ro) X-Spam-Status: No, hits=-17.6 required=5.0 tests=EMAIL_ATTRIBUTION,HTML_00_10,HTML_MESSAGE,REFERENCES, USER_AGENT_MOZILLA_UA 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/522 X-Sequence-Number: 12561 --------------050104060300000406080604 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I don't see any other solution than creating differrent columns for hours, minutes and index them. Rajesh Kumar Mallah wrote: >nopes! > >does not match any record.. >SELECT time_id from time_dimension where sql_time=cast( now() as time without time zone); >+---------+ >| time_id | >+---------+ >+---------+ >(0 rows) > > >On Saturday 22 Mar 2003 2:15 pm, Popeanga Marian wrote: > > >>Hello Mallah, >> >>Have you tryed this : >> >> SELECT time_id from time_dimension where sql_time=cast(now() as time without time zone); >> >>Your tabel is indexed for a time h:m:s and you should to have a where >>clause with a time value like this index is defined. Other way you will >>have a filter scan. >> >>Rajesh Kumar Mallah wrote: >> >> >> >>>Hi everyone, >>> >>>I have a table time_dimension loaded with 1440 records >>>i record for each min in 24 hrs >>> >>> >>>CREATE TABLE general.time_dimension >>>( >>> time_id integer not null , >>> sql_time time(0) without time zone not null, >>> PRIMARY KEY (time_id), >>> UNIQUE (sql_time) >>>); >>> >>>sample data is like, >>> >>>tradein_clients=# SELECT * from time_dimension limit 10; >>>+---------+----------+ >>>| time_id | sql_time | >>>+---------+----------+ >>>| 1 | 00:00:00 | >>>| 2 | 00:01:00 | >>>| 3 | 00:02:00 | >>>| 4 | 00:03:00 | >>>| 5 | 00:04:00 | >>>| 6 | 00:05:00 | >>>| 7 | 00:06:00 | >>>| 8 | 00:07:00 | >>>| 9 | 00:08:00 | >>>| 10 | 00:09:00 | >>>+---------+----------+ >>>(10 rows) >>> >>>To get current_time_id i use a query like >>>SELECT time_id from time_dimension where sql_time=date_trunc('minute' , cast(now() as time without time zone) ); >>> >>>it works but uses seq_scan >>>Seq Scan on time_dimension (cost=0.00..35.00 rows=5 width=4) (actual time=4.75..8.16 rows=1 loops=1) >>> Filter: ((sql_time)::interval = date_trunc('minute'::text, ((now())::time without time zone)::interval)) >>>Total runtime: 8.20 msec >>> >>>whereas a query like >>> >>>explain analyze SELECT time_id from time_dimension where sql_time='13:13:13'; >>>+------------------------------------------------------------------------------------------------------------------------------------------+ >>>| QUERY PLAN | >>>+------------------------------------------------------------------------------------------------------------------------------------------+ >>>| Index Scan using time_dimension_sql_time_key on time_dimension (cost=0.00..4.82 rows=1 width=4) (actual time=0.03..0.03 rows=0 loops=1) | >>>| Index Cond: (sql_time = '13:13:13'::time without time zone) | >>>| Total runtime: 0.06 msec >>>-------------------------------------------------------------------------------------------------------------------------------------------- >>>take much less. >>> >>>can anyone tell be the best way to get time_id from time_dimension for current_time. >>> >>>any help is appreciated. >>> >>>Regds >>>Mallah. >>> >>> >>> >>> >>> >>> >>-- >>Popeanga Marian >>DBA Oracle >>CNLO Romania >> >> >> >> >> >> >> > > > -- Popeanga Marian DBA Oracle CNLO Romania --------------050104060300000406080604 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
          I don't see any other solution than creating differrent columns for hours, minutes and index them.



Rajesh Kumar Mallah wrote:
nopes!

does not match any record..
SELECT  time_id from time_dimension where sql_time=cast( now() as time without time zone);
+---------+
| time_id |
+---------+
+---------+
(0 rows)


On Saturday 22 Mar 2003 2:15 pm, Popeanga Marian wrote:
  
Hello Mallah,

Have you tryed this :

      SELECT  time_id from time_dimension where sql_time=cast(now() as time without time zone);

Your tabel is indexed for a time h:m:s and you should to have a where 
clause with a time value like this index is defined. Other way you will 
have a filter scan.

Rajesh Kumar Mallah wrote:

    
Hi everyone,

I have a table time_dimension loaded with 1440 records
i record for each min in 24 hrs


CREATE TABLE general.time_dimension
(
       time_id integer not null ,
       sql_time time(0) without time zone  not null,
       PRIMARY KEY (time_id),
       UNIQUE (sql_time)
);

sample data is like,

tradein_clients=# SELECT * from time_dimension limit 10;
+---------+----------+
| time_id | sql_time |
+---------+----------+
|       1 | 00:00:00 |
|       2 | 00:01:00 |
|       3 | 00:02:00 |
|       4 | 00:03:00 |
|       5 | 00:04:00 |
|       6 | 00:05:00 |
|       7 | 00:06:00 |
|       8 | 00:07:00 |
|       9 | 00:08:00 |
|      10 | 00:09:00 |
+---------+----------+
(10 rows)

To get current_time_id  i use a query like
SELECT  time_id from time_dimension where sql_time=date_trunc('minute' , cast(now() as time without time zone) );

it works but uses seq_scan
Seq Scan on time_dimension  (cost=0.00..35.00 rows=5 width=4) (actual time=4.75..8.16 rows=1 loops=1)      
  Filter: ((sql_time)::interval = date_trunc('minute'::text, ((now())::time without time zone)::interval)) 
Total runtime: 8.20 msec

whereas a query like

explain analyze SELECT  time_id from time_dimension where sql_time='13:13:13';
+------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                QUERY PLAN                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------+
| Index Scan using time_dimension_sql_time_key on time_dimension  (cost=0.00..4.82 rows=1 width=4) (actual time=0.03..0.03 rows=0 loops=1) |
|   Index Cond: (sql_time = '13:13:13'::time without time zone)                                                                            |
| Total runtime: 0.06 msec
--------------------------------------------------------------------------------------------------------------------------------------------
take much less.

can anyone tell be the best way to get time_id from time_dimension for current_time.

any help is appreciated.

Regds
Mallah.


 

      
-- 
Popeanga Marian
DBA Oracle
CNLO Romania





    

  

-- 
Popeanga Marian
DBA Oracle
CNLO Romania

--------------050104060300000406080604--