CODEWITHSUNDEEP
postgresql
Melinda
Melinda

Reputation: 1531

Having a problem finding those records between yesterday and today based on a UTC column value using PostgreSQL

Using PostgreSQL and trying to query a table that contains a UTC column. The UTC column is a column without the timezone as I understand it from the developer. Example in my DB for a record is (2021-08-26 13:59:26.867578). And I have to search for records that are between yesterday's date and today's date. When I tried the SQL statement below I get this error:

[42883] ERROR: operator does not exist: timestamp without time zone - integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 63

Here is my SQL statement for PostgreSQL:

SELECT omd.*
FROM "OCRMetaDatas" omd
WHERE (omd."ScannedAt")-5 BETWEEN Now()-1 and now()
ORDER BY omd."ScannedAt" desc;

Any help/direction would be appreciated. Thanks.

Upvotes: 0

Views: 86

Answers (2)

fpietka
fpietka

Reputation: 1043

You can't operate integer with datetimes. Here you are trying to do that twice:

(omd."ScannedAt")-5

and

 Now()-1

You should use INTERVAL with datetime there, such as:

SELECT omd.*
FROM "OCRMetaDatas" omd
WHERE (omd."ScannedAt")- '5 days'::INTERVAL BETWEEN Now()- '1 day'::INTERVAL and now()
ORDER BY omd."ScannedAt" desc;

Upvotes: 2

Adrian Klaver
Adrian Klaver

Reputation: 19665

As illustration of how to use at time zone:

--My TimeZone
show TimeZone;
 TimeZone  
------------
 US/Pacific

select '2021-08-26 13:59:26.867578'::timestamp;
timestamp          
----------------------------
 2021-08-26 13:59:26.867578

select '2021-08-26 13:59:26.867578'::timestamp at time zone 'UTC';
           timezone            
-------------------------------
 2021-08-26 06:59:26.867578-07

select now();
              now              
-------------------------------
 2021-08-26 09:23:57.818477-07

at time zone will normalize the timestamps to the same time zone.

Upvotes: 1

Related Questions