I just wanted to know the difference between these two codes:

select count (user_id) from tb_users  where 
creation_date :: date between '2022-05-15' and '2022-06-15' 

Result: 41,232

select count (user_id) from tb_users  where 
creation_date between '2022-05-15' and '2022-06-15' 

Result: 40,130

As far as I see, it is related with the timestamp, but I do not understand the difference.

Thank you!

CodePudding user response：

When you ask Postgres to implicitly coerce a DATE value to a TIMESTAMP value - the hours, minutes and seconds are set to zero.
In the first query, you explicitly cast the creation date to DATE which is successfully compared to the provided DATE values.
In the second query, the creation date is of type TIMESTAMP and so PostgreSQL converts your DATE values to TIMESTAMP values and the comparison becomes

creation_date >= '2022-05-15 00:00:00' AND creation_date <= '2022-06-15 00:00:00'

Obviously, this produces different resultset than the first query.

CodePudding user response：

Your column creation_date in the table is most probably in timestamp format, which is '2022-05-15 00:00:00'. By adding ::date <- you are casting your timestamp format to date format: '2022-05-15'.

You can read more about casting data types here: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/