0

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')
chuckrocks
  • 33
  • 5
  • 1
    This would be soooo much easier if you didn't use the dreaded unix epoch, but proper `timestamp` columns. https://blog.sql-workbench.eu/post/epoch-mania/ –  Dec 08 '21 at 20:14
  • 1
    There are two forms of `to_timestamp()`': 1) [to_timestamp 1](https://www.postgresql.org/docs/current/functions-datetime.html) takes an `epoch` value which works here `to_timestamp(loggeddate)` 2) [to_timestamp 2](https://www.postgresql.org/docs/current/functions-formatting.html) that takes a string and applies a string template to it to create a `timestamp`. That is what is failing here `to_timestamp('2021-12-01 00:00:00')` as you did provide a template. – Adrian Klaver Dec 08 '21 at 21:09
  • 1
    By the way, your code has another problem. Your query will fail to report events happening between 23:59:59.0 and 00:00:00.0. Better to define your spans of time using the Half-open approach, where the beginning is *inclusive* while the ending is *exclusive*. So a day starts with the first moment of the day and runs up to, but does not include, the first moment of the next day. For days as seen in UTC (an offset of zero hours-minutes-seconds) that means `2021-12-01 00:00:00` to `2021-12-02 00:00:00`. Bonus tip: Be aware that in some time zones on some dates, the day does *not* start at 00:00. – Basil Bourque Dec 08 '21 at 22:05
  • 1
    No you don't understand, `to_timestamp(double precision)` means the function accepts a `double precision` argument in your case `loggeddate` and outputs a `timestamp with time zone` value. When you are doing `loggeddate between to_timestamp(...` you are comparing an integer(`loggeddate`) to a `timestamp` produced by `to_timestamp`. You need to do `to_timestamp(loggeddate) between to_timestamp(...` to make all the values in the comparison be the same type. – Adrian Klaver Dec 09 '21 at 16:39

1 Answers1

0

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')
SGiux
  • 619
  • 3
  • 10
  • 34
  • Hey, thanks for your answer, I tried incorporating this into my query. Please view the edit to my original post. Thanks. – chuckrocks Dec 09 '21 at 10:12
  • 1
    But what does loggdate contain? A unix timestamps - integers? If so why are you trying to do loggeddate between to_timestamp(... ??? You need to convert loggeddate first -> to_timestamp(loggeddate) between to_timestamp(... – SGiux Dec 09 '21 at 14:02
  • The penny has dropped and I totally understand where I was going wrong now, thanks SGiux – chuckrocks Dec 09 '21 at 18:12