How to query by time in postgresql

In PostgreSQL, querying by time is a common operational requirement. Through reasonable time query, you can quickly filter out the data that meets the conditions. When using PostgreSQL for time query, you can use a variety of methods, such as using comparison operators, using date functions, etc. For example, you can use the "=" operator to query data on a specific date, use the "BETWEEN" operator to query data within a certain time range, or use date functions such as "date_trunc" to be precise to a certain time unit. Inquire. In short, mastering these skills can make us perform time queries more efficiently.

Question content

device_id	device_created_at
10e7983e-6a7b-443f-b0fe-d5e6485a502c	2022-08-10 20:55:16.695

I have a table where my date/time is in the format: 2022-08-10 20:55:16.695 This is an object with a timestamp. I tried the following query but no rows were returned:

select * from device where to_char(device_created_at,'yyyy-mm-dd hh24:mi:ss.fff') = '2022-08-10 20:55:16.695'

The type of device_created_at is "timestamp without time zone"

How to query based on timestamp in postgressql?

Workaround

Try comparing timestamp values ​​instead of strings:

SELECT * 
FROM device 
WHERE device_created_at = CAST('2022-08-10 20:55:16.695' AS TIMESTAMP)

See the demo here.

The above is the detailed content of How to query by time in postgresql. For more information, please follow other related articles on the PHP Chinese website!