Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E51AD9FB2C6 for ; Fri, 10 Nov 2006 14:34:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 06338-04 for ; Fri, 10 Nov 2006 18:34:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 2D6039FB2C4 for ; Fri, 10 Nov 2006 14:34:29 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so533474ugf for ; Fri, 10 Nov 2006 10:34:28 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=nlCS8O7E3mEXZ5hvvivITEAaShsmMXgfD8cvqOvY4vtXqXrgxN10dVUHPlAvZdk6A1CjMH1j3JdGPTv6ONUzqzYfF/hAkaf9VmkAEFos6OivXCT4D6hBysa2zOmuUVDWnYKeoy2+EbEgE4qZc2TpNbKX11/rKD3o6IpETH2vmlw= Received: by 10.67.19.17 with SMTP id w17mr3777302ugi.1163183667254; Fri, 10 Nov 2006 10:34:27 -0800 (PST) Received: by 10.67.118.11 with HTTP; Fri, 10 Nov 2006 10:34:27 -0800 (PST) Message-ID: <55c095e90611101034r7dc6bf1btba72aa24f9300593@mail.gmail.com> Date: Fri, 10 Nov 2006 16:34:27 -0200 From: "Ezequias Rodrigues da Rocha" To: "A. Kretschmer" Subject: Re: Between and miliseconds (timestamps) Cc: pgsql-sql@postgresql.org In-Reply-To: <20061110152427.GA3634@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_78022_20306486.1163183667163" References: <55c095e90611100628m1322eea5jeeca3a94d90e1eb1@mail.gmail.com> <20061110144458.GA2909@a-kretschmer.de> <55c095e90611100650q3ec0ec27tc0f8e94a0eb032eb@mail.gmail.com> <20061110152427.GA3634@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/117 X-Sequence-Number: 26769 ------=_Part_78022_20306486.1163183667163 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Ok thank you very much, but the suggestion (SQL:when field =3D '2006-09-06'::date) does not retrieve any row. It does not retrive any erro= r too, but the suggestion does not applied to my requisition. The second suggestion is quite correct but I would like to use the between expression. Best regards to both Ezequias 2006/11/10, A. Kretschmer : > > am Fri, dem 10.11.2006, um 12:50:38 -0200 mailte Ezequias Rodrigues da > Rocha folgendes: > > > select * from base.table > > > where when > > > between > > > '2006-09-06 00:00: 00.000000' > > > and > > > '2006-09-06 23:59:59.999999' > > > order by 2 > > > > > > Is there a simplest way or not ? > > > > Yes. ... where when =3D '2006-09-06'::date. > > > > > > I didn't understand you. Wha you mean '::date' ? > > This is a so called CAST. For example: > > test=3D# select now(); > now > ------------------------------- > 2006-11-10 16:19:49.543082+01 > (1 row) > > test=3D# select now()::date; > now > ------------ > 2006-11-10 > (1 row) > > > Read more about this: > 16:23 < akretschmer> ??cast > 16:23 < rtfm_please> For information about cast > 16:23 < rtfm_please> see > http://www.postgresql.org/docs/current/static/sql-createcast.html > 16:23 < rtfm_please> or > http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SY= NTAX-TYPE-CASTS > > > > > > And if I have a period of time bigger than 1 day ? > > BETWEEN includes the boundaries, if you have full days so say something > like 'between first_day and last_day'. > > Do you need time-boundaries? Rewrite your query to something like > 'where time_column > time_1 and time_column < time_2' > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > --=20 =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- A pior das democracias ainda =E9 melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ------=_Part_78022_20306486.1163183667163 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Ok thank you very much, but the suggestion (SQL:when field =3D '2006-09-06'::date) does not retrieve any row= . It does not retrive any error too, but the suggestion does not applied to= my requisition.

The second suggestion is quite correct but I would like to use the = between expression.

Best regards to both
Ezequias

2006/11/10, A. Kretschmer < andreas.kretschmer@schollglas.com>:
am  Fri, dem 10.11.2006, um 12:50= :38 -0200 mailte Ezequias Rodrigues da Rocha folgendes:
>     > select * from base.table
> = ;    > where when
>     > be= tween
>     > '2006-09-06 00:00: 00.000000'>     > and
>     >= '2006-09-06 23:59:59.999999'
>     > order by= 2
>     >
>     > = Is there a simplest way or not ?
>
>     Ye= s. ... where when =3D '2006-09-06'::date.
>
>
> I didn't = understand you. Wha you mean '::date' ?

This is a so called CAST. Fo= r example:

test=3D# select now();
       = ;       now
-------------------------= ------
2006-11-10 16:19:49.543082+01
(1 row)

test=3D# select = now()::date;
    now
------------
2006-11-10<= br>(1 row)


Read more about this:
16:23 < akretschmer> ??cast
16:23 <= ; rtfm_please> For information about cast
16:23 < rtfm_please> = see http://www.postgresql.org/docs/current/static/sql-createcast.html
16= :23 < rtfm_please> or http://www.postgresql= .org/docs/current/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS


>
> And if I have a period of time bigger than 1 d= ay ?

BETWEEN includes the boundaries, if you have full days so say s= omething
like 'between first_day and last_day'.

Do you need time-= boundaries? Rewrite your query to something like
'where time_column > time_1 and time_column < time_2'


= Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/= 47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: =   0x3FFF606C, privat 0x7F4584DA  =20 http://wwwkeys.de.pgp.net

= ---------------------------(end of broadcast)---------------------------TIP 6: explain analyze is your friend



--
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-= =3D-=3D-
          &nb= sp;            =            Atenciosa= mente (Sincerely)
         =             &nb= sp;  Ezequias Rodrigues da Rocha
=3D-=3D-=3D-=3D-=3D-=3D-=3D-= =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D= -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-
A pior das democracias ainda =E9 melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships=
http://ezequiasrocha.bl= ogspot.com/ ------=_Part_78022_20306486.1163183667163--