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?
CodePudding user response:
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')
CodePudding user response:
You have two errors in your WHERE clause: first to_timestamp()
can't be used without a format mask. That's one of the reasons I prefer ANSI timestamp literals, e.g. timestamp '2021-12-01 00:00:00'
When dealing with timestamp range conditions, it's typically better to avoid the BETWEEN operator and use >=
and <
with the day following the upper limiter.
The root cause of the error message is however that you are comparing a number to a timestamp. If you want a readable WHERE condition, you also need to convert your unix epoch to a timestamp there:
and to_timestamp(loggeddate) >= timestamp '2021-12-01 00:00:00'
and to_timestamp(loggeddate) < timestamp '2021-12-02 00:00:00'