[SOLVED] Postgres; select integers representing date and time query using to_timestamp between '2021-12-01 00:00:00' and '2021-12-01 23:59:59'

Postgres; select integers representing date and time query using to_timestamp between '2021-12-01 00:00:00' and '2021-12-01 23:59:59'

I have columns that contain unix timestamps - integers representing the number of seconds since the epoch. They look like this: 1638888715. I am comfortably converting this int into a timestamp using the to_timestamp() function and arriving at output that looks like this: 2021-12-07 13:51:55+00

I am trying to select data between a 24 hour period: 2021-12-01 00:00:00 and 2021-12-01 23:59:59

My query looks like this:

SELECT to_timestamp(loggeddate), to_timestamp(trxdate), [column a], [column b], [column c], [column d]
FROM [this table]
where [column a] like 'some criteria'
or [column a] like 'some other criteria'
and loggeddate between to_timestamp('2021-12-01 00:00:00') and to_timestamp('2021-12-01 23:59:59')

The error I get is:

ERROR:  invalid input syntax for type double precision: "2021-12-01 00:00:00"
LINE 5: and loggeddate between to_timestamp('2021-12-01 00:00:00') a...
                                            ^

Please could somebody explain the blindingly obvious?

:::EDIT1:::

Thanks for the responses, I understand the difference between to_timestamp and to_timestamp(double precision) now. The integers are being converted to double precision timestamps (I have the timezone +00 present at the end of the time).

With the last line of my query looking like:

loggeddate between to_timestamp('2021-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_timestamp('2021-12-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

I am receiving the following error:

ERROR:  operator does not exist: integer >= timestamp with time zone
LINE 5: and loggeddate between to_timestamp('2021-12-01 00:00:00', '...
                       ^

I have managed to find a work around that gets me what I want; by writing the select into a view without the date time filter, the integers are transformed to a date time that can be queried using my 'between' statement.

CREATE VIEW trx_data as
SELECT to_timestamp(loggeddate), to_timestamp(trxdate), [column a], [column b], [column c], [column d]
FROM [this table]
where [column a] like 'some criteria'
or [column a] like 'some other criteria'

Query the view:

select * from trx_data
where "logged date" between '2021-12-06 00:00:00' and '2021-12-07 00:00:00'
order by "logged date"

Output looks like:

"2021-12-06 00:00:02+00"    "2021-12-05 23:00:01+00"    "THIS EVENT TYPE"   "THIS EVENT NAME"   "THIS AREA" "THIS UNIT"

It would be nice to be able to do this all in one step rather than write the data into a view before it can be queried, I'd still appreciate any pointers on working with the double precision to_timestamp to achieve a single query that lands at the same result.

Cheers

EDIT2 - Working; thanks to SGiux, Adrian and Basil

Working query looks like:

SELECT to_timestamp(loggeddate), to_timestamp(trxdate), [column a], [column b], [column c], [column d]
FROM [this table]
where [column a] like 'some criteria'
or [column a] like 'some other criteria'
and to_timestamp(loggeddate)
between to_timestamp('2021-12-01 00:00:00')
and to_timestamp('2021-12-02 00:00:00')

Solution

PostgreSQL simply doesn't know how to read the string you passed as parameter of the function. Try this:

SELECT to_timestamp('2021-12-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

Response to EDIT1:

You cannot compare an integer between two timestamp. Try this:

to_timestamp(loggeddate) 
    between to_timestamp('2021-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and 
    to_timestamp('2021-12-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')