Thread: Between and miliseconds (timestamps)

Hi list,<br /><br />I noticed that when we use the keyword between in SQL statements we must considerer all the
milisecondswith 6 digits.<br /><br />I would like to know if we want to make a day selection we must use allways the
maxof possibilities like: <br /><br /><br />select * from base.table<br />where when<br />between <br />'2006-09-06
00:00:00.000000'<br />and<br />'2006-09-06 23:59:59.999999'<br />order by 2<br clear="all" /><br />Is there a simplest
wayor not ?<br /><br /> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- <br
/>                                  Atenciosamente(Sincerely)<br />                        Ezequias Rodrigues da
Rocha<br/> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br />A pior das democracias ainda é
melhordo que a melhor das ditaduras <br />The worst of democracies is still better than the better of dictatorships<br
/><ahref="http://ezequiasrocha.blogspot.com/">http://ezequiasrocha.blogspot.com/</a> 

am  Fri, dem 10.11.2006, um 12:28:53 -0200 mailte Ezequias Rodrigues da Rocha folgendes:
> Hi list,
> 
> I noticed that when we use the keyword between in SQL statements we must
> considerer all the miliseconds with 6 digits.
> 
> I would like to know if we want to make a day selection we must use allways the
> max of possibilities like:
> 
> 
> 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 = '2006-09-06'::date.



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

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 = '2006-09-06'::date.
> 
> 
> I didn't understand you. Wha you mean '::date' ?

This is a so called CAST. For example:

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

test=# 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-SYNTAX-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

Ok thank you very much, but the suggestion (SQL:when field = '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




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Hi Ezequias,

On Fri, 2006-11-10 at 16:34 -0200, Ezequias Rodrigues da Rocha wrote:
> Ok thank you very much, but the suggestion (SQL:when field =
> '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. 

I think you want to cast the field, not the constant, e.g.,

testdb=> select * from t2 where date(tm) = '2006-9-6';             tm               
-------------------------------2006-09-06 00:00:01-042006-09-06 23:59:59.999999-04
(2 rows)

Joe

Great Joe. That is what I want.

Now I can do a simple select (with between) statement.

Now my query that works is:

select * from base.tb1
where date(tm) between '2006-09-05' and '2006-09-06'
order by 1

Thank you very much Joe





--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/