Filter exact date/time in postgresql-CodePudding

I'd like to filter out a record based on update time in Postgresql. My table, under update_timestamp field is storing the following dates:

update_timestamp
April 19, 2022, 7:03 PM
April 19, 2022, 7:01 PM

The timestamp for the second row is 2022-04-19 19:01:11:

SELECT to_char(update_timestamp,'YYYY-MM-DD HH24:MI:SS')
from my_table

to_char
2022-04-19 19:03:44
2022-04-19 19:01:11

Then, if I want to query the second date and time, I am converting string to timestamp:

select update_timestamp
from my_table 
where update_timestamp = to_timestamp('2022-04-19 19:01:11','YYYY-MM-DD HH24:MI:SS')

As alternative, I can try another syntax:

select update_timestamp
from my_table 
where update_timestamp = '2022-04-19 19:01:11'::timestamp

What is the proper way to query date/time when passing a string? Am I doing something wrong?

Why a query such as this one is giving me the row I need (2022-04-19 19:01:11):

select update_timestamp
from my_table
where update_timestamp < '2022-04-19 19:03:00'

However, a query including the specific value as the following does not return any value:

select update_timestamp
from my_table
where update_timestamp = '2022-04-19 19:01:11'

Is the postgresql engine just able to query date ranges? This is working fine to get 2022-04-19 19:01:11:

select update_timestamp
from my_table
where update_timestamp > '2022-04-19 19:01:10' and update_timestamp < '2022-04-19 19:01:12'

Thanks

CodePudding user response：

For a specific value of timestamp you should use date_trunc function, since timestamp has precision of microseconds

so for your example should be:

select update_timestamp
from my_table
where date_trunc('second',update_timestamp) = '2022-04-19 19:01:11';