pg-promise , how to select with a where ... like date%

Question

Im a bit stuck trying to get a select of the following:

id(int)  balance(int)  datetime(timestamp without timezone)
7        153           "2020-08-24 20:15:49"
8        115           "2020-08-24 20:16:13"
9        105           "2020-08-24 20:17:14"

I want to select doing this

let date = moment().format("YYYY-MM-DD")
console.log(date) // 2020-08-24
let selectAddressSnapshotByDate = new PQ({text: "SELECT balance, datetime FROM table where datetime like '$1%' ", values: [date.toString()]})

I basically want to get the values whose datetime starts with the date value of the programm

Right now im receiving the following error:

error: operator does not exist: timestamp without time zone ~~ unknown

Answer 1

To begin with, you are not using the query parameter properly. The parameter should not be surrounded with single quotes.

Then: if you want to deal with dates, then use date functions, not string functions such as like.

I would phrase your query as:

select balance, datetime 
from table 
where datetime >= $1::date and datetime < $1::date + '1 day'::interval