Number of records in a period of time PostgreSQL

Question

I have a PostgreSql query, which should make a count of the results that exist between that time frame (between the field "date" and the current time "now"), however the query does nothing but count all the records without applying the filter, does anyone know what I am missing in the query?

This is the query:

SELECT count(*) from table where date between 
TO_TIMESTAMP('2022-8-1 12:00:00','YYYY-M-D HH:MI:SS') and now();

Result: 15,480 (all results, does not apply filter "between")

Greetings and thanks

Answer 1

select TO_TIMESTAMP('2022-8-1 12:00:00','YYYY-M-D HH:MI:SS') ;
 to_timestamp      
------------------------
 2022-01-01 00:00:00-08

Per template patterns here Format functionsTable 9.26. Template Patterns for Date/Time Formatting it needs to be:

select TO_TIMESTAMP('2022-08-01 12:00:00','YYYY-MM-DD HH24:MI:SS') ;
to_timestamp      
------------------------
 2022-08-01 12:00:00-07

Though it would be easier to do something like:

select '2022-8-1 12:00:00'::timestamptz;
      timestamptz       
------------------------
 2022-08-01 12:00:00-07

Ending up with:

SELECT count(*) from table where date between 
'2022-8-1 12:00:00'::timestamptz and now();