Truncating time when querying a PostgreSQL database

How to truncate time in a PostgreSQL query to get and compare dates without the time part.

When you have to compare dates in a SQL query, you often want to ignore the time part. For example if you have two very common fields like created_at and updated_at and you want to know if they are the same day, you have to truncate the time part.

How to do this depends on the database you are using. Here is how you can do it in PostgreSQL.

1
2
3
4
SELECT * 
FROM users 
WHERE DATE(created_at) <> DATE(updated_at)
ORDER BY created_at DESC;

This query will return all the users where the created_at and updated_at fields are not on the same day, ignoring the time part.

comments powered by Disqus
source code available on GitHub
Built with Hugo
Theme Stack designed by Jimmy