X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id DE64F9DC99C for ; Tue, 28 Feb 2006 15:51:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09773-02 for ; Tue, 28 Feb 2006 15:51:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.199]) by postgresql.org (Postfix) with ESMTP id 6E8F99DC823 for ; Tue, 28 Feb 2006 15:51:00 -0400 (AST) Received: by xproxy.gmail.com with SMTP id i30so781581wxd for ; Tue, 28 Feb 2006 11:51:01 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=PpAh8dRcxj1MlZf5pWJQ/SLTIKdhkfU52rJ2b6uWjnonJfF1jVJ+v0KI2GkfQ5yi/wStyncWP4gLKQVLFLeIjV5Ju1EhNswQ/n1F8IEezTSyAJrwUP4t1TflnZFaozcSB3j26CE8pTar2kjRZb63aKY5YiFFNYk85JiEpzSPtfg= Received: by 10.70.60.20 with SMTP id i20mr1002619wxa; Tue, 28 Feb 2006 11:51:01 -0800 (PST) Received: by 10.70.129.20 with HTTP; Tue, 28 Feb 2006 11:51:01 -0800 (PST) Message-ID: Date: Tue, 28 Feb 2006 16:51:01 -0300 From: "Rodrigo Llanos" To: pgsql-php@postgresql.org Subject: Re: selecting records by timestamp (date) In-Reply-To: <20060228181626.39524.qmail@web60214.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15266_33434020.1141156261685" References: <20060228181626.39524.qmail@web60214.mail.yahoo.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200602/32 X-Sequence-Number: 3448 ------=_Part_15266_33434020.1141156261685 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline maybe the postgres version in the server is different On 2/28/06, Jovan Kostovski wrote: > > Hi, > > I have the following table running on postgres 8.04: > > CREATE TABLE ValueLog > ( > idValueLog int4 NOT NULL DEFAULT nextval (' > public.idValueLog_seq'::text), > PPx integer, > iValue integer DEFAULT -1, > fValue real DEFAULT -1, > t_tag timestamp without time zone, > t_arr timestamp without time zone, > cot integer, > ack boolean, > blk boolean, > qd_iv boolean, > qd_nt boolean, > qd_sb boolean, > qd_bl boolean, > qd_ov boolean, > CONSTR AINT idValueLog PRIMARY KEY (idValueLog) > ) > WITHOUT OIDS; > ALTER TABLE ValueLog OWNER TO $admin_user;" > > I've created the following SQL statement to select the records newer then > a given date: > > select almvalue.almname , ppoint.ppdimstr, valuelog.ack, valuelog.blk, > to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from ppoint, valuelog, > almvalue where ppoint.ppx =3D valuelog.ppx and almvalue.almvalue =3D > valuelog.ivalue and valuelog.ppx =3D $db_ppx and to_char(valuelog.t_arr,= 'YYYYMMDD') > >=3D '$ts > > $ts is a string, the date value input by the user and formated YYYYMMDD > > I've developed the web site on my Ubuntu box, but when I uploaded the sit= e > on the server Fedora Core 3, I had problems with the date filtering. Runn= ing > psql on the server I've noticed that there were more data in the select * > from value log results, field t_arr something like MM-DD-YYYY-dsfsdfd > (something else which I don't know what it is) > I think that the local time settings on the server makes me the troubles. > > What's the solution? > > I've even checked the result of: > select to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from valuelog; > > but instead of data I got empty data set. Why? > > What's the best way to do filtering (selecting) data by time? > > TIA, Jovan > > ------------------------------ > Yahoo! Mail > Bring photos to life! New PhotoMail > makes sharing a breeze. > -- Atte. Rodrigo A. Llanos N. Saludos. ------=_Part_15266_33434020.1141156261685 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline maybe the postgres version in the server is different

On 2/28/06, Jovan Kostovsk= i <chom_devel@yahoo.com&= gt; wrote:
 Hi,<= br>
I have the following table running on postgres 8.04:

 &n= bsp; CREATE TABLE ValueLog=20
    (
      idValueLog int4= NOT NULL DEFAULT nextval ('public.idValueLog_seq'::text),
  =     PPx integer,
      iValue i= nteger DEFAULT -1,
      fValue real DEFAULT -= 1,
      t_tag timestamp without time zone,=20
      t_arr timestamp without time zone,
&= nbsp;     cot integer,
    &nbs= p; ack boolean,
      blk boolean,
 &= nbsp;    qd_iv boolean,
      q= d_nt boolean,
      qd_sb boolean,
 &= nbsp;    qd_bl boolean,
      q= d_ov boolean,=20
      CONSTR AINT=20 idValueLog PRIMARY KEY (idValueLog)
    )
 &nb= sp;      WITHOUT OIDS;
     &nb= sp;  ALTER TABLE ValueLog OWNER TO $admin_user;"

I've cre= ated the following SQL statement to select the records newer then a given d= ate:

select almvalue.almname , ppoint.ppdimstr, valuelog.ack, valuelog.b= lk, to_char(valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from ppoint, valuelog, = almvalue where ppoint.ppx =3D valuelog.ppx and almvalue.almvalue =3D valuel= og.ivalue and valuelog.ppx =3D $db_ppx and  to_char(valuelog.t_arr,'YYYYMMDD') = >=3D '$ts

$ts is a string, the date value input by the user and f= ormated YYYYMMDD

I've developed the web site on my Ubuntu box, but w= hen I uploaded the site on the server Fedora Core 3, I had problems with th= e date filtering. Running psql on the server I've noticed that there were m= ore data in the select * from  value log results, field t_arr somethin= g like MM-DD-YYYY-dsfsdfd (something else which I don't know what it is)
I thi= nk that the local time settings on the server makes me the troubles.
What's the solution?

I've even checked the result  of:
sele= ct to_char( valuelog.t_arr,'DD.MM.YYYY HH24:MI:SS') from  valuelog;

but ins= tead of data I got empty data set. Why?

What's the best way to do fi= ltering (selecting) data by time?

TIA, Jovan

=09=09


Yahoo! Mail
Bring photos to life! New PhotoMail makes = sharing a breeze.=20




--
Atte. Rodrig= o A. Llanos N.

Saludos. ------=_Part_15266_33434020.1141156261685--