Search code examples
oracle-databasepostgresqlansi-sql

How to rewrite Oracle specific function TO_NUMBER to ANSI SQL

There is a legacy software running against an Oracle database. I'm trying to generalize it for Docker tests, so I prepared a PostgreSQL image with the database schema. Now I'm trying to run that application against the Postgres DB, but there is the following error:

org.postgresql.util.PSQLException: ERROR: function to_number(text) does not exist

So I looked into the code and found the following construct in some selects:

to_number(to_char({0},'HH24MI')) <= to_number(to_char({1},'HH24MI'))
to_number(to_char({0},'HH24MI')) > to_number(to_char({1},'HH24MI'))

Since I'm not a database specialist, I assume that this TO_NUMBER is an Oracle-specific function. In this case it works as a filter to return records for the specified time range.

Is there a way how could I simply replace this TO_NUMBER function with some similar function in ANSI SQL?

Solution

  • Instead of the Oraclism

    to_number(to_char(current_timestamp, 'HH24MI'))

    you could use this standard SQL construct:

    100 * EXTRACT(HOUR   FROM current_timestamp)
    + EXTRACT(MINUTE FROM current_timestamp)