Trying to compare timestamp value data-CodePudding

In my Postgresql database, I have a column:

date_emailed timestamp NULL

When I insert it into the table, I use the value 'now()' for date_emailed column. Here is the example data:

When I try to query the row highlighted with the value '2022-10-17 06:39:10.036' I only get the value if I use >= and not =. For example:

Using '>=':

select date_emailed
from passdown.category_account_entry 
where 
date_emailed is not null
and date_emailed >= '2022-10-17 06:39:10.036'::timestamp 
order by date_emailed desc;

My results return multiple rows, which is correct when using '>=':

But when I try to just use '=' to get the exact row, I get zero results:

select date_emailed
from passdown.category_account_entry 
where 
date_emailed is not null
and date_emailed = '2022-10-17 06:39:10.036'::timestamp 
order by date_emailed desc;

So I thought I would convert it to an epoch test, and I noticed that the epoch value of date_emailed is different than the string value '2022-10-17 06:39:10.036'

select date_emailed,
date_part('epoch', date_emailed) as epoch_from_date_emailed,
date_part('epoch', '2022-10-17 06:39:10.036'::timestamp) as from_value
from passdown.category_account_entry 
where 
date_emailed is not null
and date_emailed >= '2022-10-17 06:39:10.036'::timestamp 
order by date_emailed desc;

So what is different from the string value '2022-10-17 06:39:10.036' to the data in date_emailed? Am I missing something? Any tips or advise would be greatly appreciated.

CodePudding user response：

As you can see in your sample data, it's a matter of precision, so using date_trunc function it could be solved

select date_trunc('milliseconds', date_emailed)
= '2022-10-17 06:39:10.036'::timestamp 
from passdown.category_account_entry;

so now this sentence is true.