0

I am trying to retrieve data from a column with integer values by getting the difference between the current_timestamp and the values in the column. This works in DB2 by simply stating the value as hour

TRANS_DATETIME > (CURRENT_TIMESTAMP - my_column_alias HOURS).

However using the same line in PostgreSQL returns the error below. Any ideas on how to modify query for PostgreSQL?

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

1
  • Hi, and welcome to dba.se! Please tell us what server you are using - various functions vary (greatly) from RDBMS to RDBMS).
    – Vérace
    Nov 21, 2022 at 10:31

1 Answer 1

1

You can use make_interval() to construct an interval from a given number of hours that's stored in a column:

trans_datetime > current_timestamp - make_interval(hours => my_column)
1
  • 1
    This worked perfectly. Thank you soo much
    – kunleoju
    Nov 21, 2022 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.