Postgres Pro
Downloads
Home   >   mailing lists

Thread: DAte use in For loop

DAte use in For loop

From
"Ramesh PAtel "
Date:
Hi All

please give one  help

I want to us date variable in for loop Condition. in PHP

But it not working Properly.
$dt='2003-04-22' ;
$to='2003-04-30';

FOR ( $dt=$dt; $dt > $to ;  )
{
print "XYz";
}

Please Help me

and how to file date diff in Day please Help

Ramesh Patel

Re: DAte use in For loop

From
Jean-Michel Chabanne
Date:
Ramesh PAtel wrote:
> Hi All
>
> please give one  help
>
> I want to us date variable in for loop Condition. in PHP
>
> But it not working Properly.
> $dt='2003-04-22' ;
> $to='2003-04-30';
>
> FOR ( $dt=$dt; $dt > $to ;  )
> {
> print "XYz";
> }
>
> Please Help me
>
> and how to file date diff in Day please Help
>
> Ramesh Patel
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



$begin_d = mktime(2, 0, 0, 4, 22, 2003);
$end_d = mktime(2, 0, 0, 4, 30, 2003);

while($begin_d <= $end_d)
{
    echo date("Y-m-d",$begin_d) . "\n";
    $begin_d += 86400;
}

For PHP informations, look at comp.lang.php


--
Jean-Michel Chabanne
77450 MONTRY (FRANCE)
48" 54' N - 2" 49' E
Powered by Linux

Type casting and indexes: bug?

From
"Marc Mitchell"
Date:
We ran into a problem which seems to point to a defect relating to
performing explicit type casts on columns that have alternate indexes on
them.  We distilled down into the following simplest case:

# select version()
# ;
                          version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

#
# SELECT DISTINCT
#     EXO.add_timestamp
# FROM
#     obc_device OBC,
#     sy_external_object EXO
# WHERE
#     OBC.station_id::bpchar = EXO.reference_data_1 AND
#     EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

add_timestamp
---------------
(0 rows)

#
# SELECT DISTINCT
#     EXO.add_timestamp
# FROM
#     obc_device OBC,
#     sy_external_object EXO
# WHERE
#     TRIM(OBC.station_id) = TRIM(EXO.reference_data_1) AND
#     EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

add_timestamp
---------------------
 2003-05-01 10:53:22
....
....
....
 2003-05-01 15:24:53
 2003-05-01 15:28:19
(189 rows)


We originally thought that something was going wrong with the BETWEEN as
the bigger SQL that originally was the source of the issue would return
rows when using "add_timestamp > '2003-5-1'" and not return rows when using
"add_timestamp BETWEEN '2003-5-1' AND '2003-5-2'".  However, what we found
seemed to tie the problem to the use of an index on the type casted
"station_id" column.  When doing the "greater than" operation instead of
the BETWEEN, the query plan changed (and rightly so) such that it did not
use the alternate index on station_id.  The same effect is achieved by
performing the TRIM() function on the column.  Consider the explains of the
sqls:

# explain
# SELECT DISTINCT
#     EXO.add_timestamp
# FROM
#     obc_device OBC,
#     sy_external_object EXO
# WHERE
#     OBC.station_id::bpchar = EXO.reference_data_1 AND
#     EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

NOTICE:  QUERY PLAN:
Unique  (cost=0.00..6.94 rows=1 width=128)
  ->  Nested Loop  (cost=0.00..6.94 rows=1 width=128)
        ->  Index Scan using exo_add_timestamp on sy_external_object exo
        ->  Index Scan using obc_station_id on obc_device obc

# explain
# SELECT DISTINCT
#     EXO.add_timestamp
# FROM
#     obc_device OBC,
#     sy_external_object EXO
# WHERE
#     TRIM(OBC.station_id) = TRIM(EXO.reference_data_1) AND
#     EXO.add_timestamp BETWEEN '2003-5-1' AND '2003-5-2';

NOTICE:  QUERY PLAN:
Unique  (cost=100000000.00..100000015.07 rows=1 width=128)
  ->  Nested Loop  (cost=100000000.00..100000015.07 rows=1 width=128)
        ->  Index Scan using exo_add_timestamp on sy_external_object exo
        ->  Seq Scan on obc_device obc  (cost=100000000.00..100000008.20


This would seem to indicate some trouble with type casting indexed columns.
Right now we can use the TRIM() work around as even though the index isn't
used, the query performs acceptably fast.  However, something seems not
right.  Sorry if this is simply a known issue that has been corrected in
7.3.  With no other known issues forcing us to upgrade, we are hoping to
hold out for a stable 7.4.

Any input?

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm@eisolution.com