postgresql - Using BETWEEN operator with timestamp values in Postgres -

recently i've been informed stackoverflow user using between operator values of data type timestamp without time zone should not used. below quote.

between means >= , <= , shall not used ranges contain timestamps.

when asked explanation of thesis or link postgres documentation states i've got answer saying

why such simple thing need site documentation. sure can find many anyway if google (at least detailed posts on various forums demonstrating case)

well googled. , found nothing advise against using operator timestamp values. in fact this answer on uses them , this mailing group post.

i informed these years doing wrong. case?

as far know postgres max precision timestamp 1 microsecond - correct me if i'm wrong. aren't below statements equivalent ?

sample_date between x , y::timestamp - interval '1 microsecond' 

and

sample_date >= x , sample_date < y 

edit: sample consideration of difference. i'm aware of fact developers can miss time part, assuming 1 knows how behaves, why should not used? speaking, merely sample, i'm wondering bigger scope. i've been investigating planner , seems parsing between >= , <=.

why 1 preferably write >= , <= between in matter of results - not including time translate it?

there absolutely nothing wrong using ts between validfrom , validto instead of ts >= validform , ts <= validto. same.

i can guess, i'd warning targets different, namely whether either of (identical) clauses above right thing use.

now of course depends on trying do, clauses used identify 1 valid row particular timestamp. in case clause above wrong, because value of ts when row changed, two results.

consider this:

create table names (    id integer primary key,    val text not null,    validfrom timestamptz not null,    validto timestamptz not null );  insert names values (1, 'smith', '1985-05-02 00:00:00', '2009-01-30 00:00:00'); insert names values (2, 'jones', '2009-01-30 00:00:00', 'infinity'); 

this meant historized table of names person.

if use where clause above query name valid @ time, work for

select val names current_timestamp between validfrom , validto; 

but wrong thing for

select val names '2009-01-30' between validfrom , validto; 

that because end point of interval of validity name not part of interval. case, correct write:

select val names '2009-01-30' >= validfrom , '2009-01-30' < validto;