to_char equivalent that should work in both oracle and mysql

Question

select to_char(systimestamp-1/24,'dd-mm-yy hh24:mi:ss') from dual;

Above query works in oracle. But I also need to work it in mysql as my application should work in both databases with out changing query everytime. So is there any equivalent query for the above that would give same result and should work in both databases.

If you are using two different databases, you simply need to accept that queries will be different. Design your application accordingly. — Gordon Linoff, Apr 15 '16 at 10:51
Two different stored procedures/functions that return the same value? — jarlh, Apr 15 '16 at 10:52
@GordonLinoff Actually its an old application I am in db team and there are no changes expected except this one. Is there any work around to do so. — siva ramakrishna alla, Apr 15 '16 at 10:58
Your choices are to A) write a TO_CHAR() function for MySQL, implement SYSTIMESTAMP for MySQL, and implement the necessary math so that subtracting 1/24 from a timestamp will subtract one hour, B) write implementations of the MySQL functions DATE_FORMAT and DATEDIFF (and possibly others) for Oracle, or C) accept that different database engines do things differently. [Or you could shell out the bucks for SQLines](http://www.sqlines.com/mysql-to-oracle). Best of luck. — Bob Jarvis - Слава Україні, Apr 15 '16 at 11:19

score 0 · Answer 1 · answered Apr 15 '16 at 11:47

As mentioned in a comment, when working with two different DBMS one should accept that queries differ, because of differences in the SQL dialects.

CURRENT_TIMESTAMP, however is standard SQL and available in both DBMS. FROM DUAL is not standard SQL, but both DBMS feature it. So simply do

select current_timestamp from dual;

and use your client app to subtract an hour and display the data according to your wishes.

Another option would be to write a view in both DBMS

create view current_datetime_string as
select ... as value from dual;

and then select the string as

select value from current_datetime_string;

score 0 · Answer 2 · edited May 23 '17 at 12:07

This will work in both Oracle and MySQL:

SELECT CURRENT_TIMESTAMP - INTERVAL '1' HOUR
FROM   DUAL;

(Please note the difference between SYSTIMESTAMP and CURRENT_TIMESTAMP)

However, formatting it as a string requires TO_CHAR() in Oracle:

SELECT TO_CHAR( CURRENT_TIMESTAMP - INTERVAL '1' HOUR, 'dd-mm-yy hh24:mi:ss' )
FROM   DUAL;

and DATE_FORMAT() in MySQL:

SELECT DATE_FORMAT( CURRENT_TIMESTAMP - INTERVAL '1' HOUR, '%d-%m-%y %H:%i:%s' )
FROM   DUAL;

You can create a function in Oracle (or MySQL) that will convert one to the other:

CREATE FUNCTION DATE_FORMAT( value DATE, mask VARCHAR2 )
  RETURN VARCHAR2 DETERMINISTIC
AS
  p_mask VARCHAR2(4000) := mask;
BEGIN
  p_mask := REPLACE( p_mask, '%Y', 'YYYY' );
  p_mask := REPLACE( p_mask, '%y', 'YY' );
  p_mask := REPLACE( p_mask, '%b', 'MON' );
  p_mask := REPLACE( p_mask, '%M', 'MONTH' );
  p_mask := REPLACE( p_mask, '%m', 'MM' );
  p_mask := REPLACE( p_mask, '%a', 'DY' );
  p_mask := REPLACE( p_mask, '%d', 'DD' );
  p_mask := REPLACE( p_mask, '%H', 'HH24' );
  p_mask := REPLACE( p_mask, '%h', 'HH12' );
  p_mask := REPLACE( p_mask, '%i', 'MI' );
  p_mask := REPLACE( p_mask, '%s', 'SS' );
  RETURN TO_CHAR( value, p_mask );
END;
/

Then you can use the MySQL query in Oracle (or vice-versa, if you implement the inverse function).

If you really don't want to use a function to assist in conversion then this should work in both MySQL and Oracle:

SELECT CONCAT(
         CASE WHEN MOD( EXTRACT( YEAR   FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ), 100 ) < 10
              THEN CONCAT( '0', MOD( EXTRACT( YEAR   FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ), 100 ) )
              ELSE CONCAT( '',  MOD( EXTRACT( YEAR   FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ), 100 ) )
              END,
         '-',
         CASE WHEN EXTRACT( MONTH  FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) < 10
              THEN CONCAT( '0', EXTRACT( MONTH  FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              ELSE CONCAT( '',  EXTRACT( MONTH  FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              END,
         '-',
         CASE WHEN EXTRACT( DAY    FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) < 10
              THEN CONCAT( '0', EXTRACT( DAY    FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              ELSE CONCAT( '',  EXTRACT( DAY    FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              END,
         ' ',
         CASE WHEN EXTRACT( HOUR   FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) < 10
              THEN CONCAT( '0', EXTRACT( HOUR   FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              ELSE CONCAT( '',  EXTRACT( HOUR   FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              END,
         ':',
         CASE WHEN EXTRACT( MINUTE FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) < 10
              THEN CONCAT( '0', EXTRACT( MINUTE FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              ELSE CONCAT( '',  EXTRACT( MINUTE FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              END,
         ':',
         CASE WHEN EXTRACT( SECOND FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) < 10
              THEN CONCAT( '0', EXTRACT( SECOND FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              ELSE CONCAT( '',  EXTRACT( SECOND FROM CURRENT_TIMESTAMP - INTERVAL '1' HOUR ) )
              END
       ) AS formatted_date
FROM DUAL;

`SELECT LAST_X_HRS, to_char(max(create_date)-LAST_X_HRS/24,'dd-MON-yy hh:mi AM') start_time, to_char(max(create_date)-(LAST_X_HRS-1)/24,'dd-MON-yy hh:mi AM') end_time, SUM(TON_PER_HR)as TPH FROM DB_OPS_AGGR_DATA WHERE MODULE='BB' AND AREA IS NULL GROUP BY LAST_X_HRS ORDER BY LAST_X_HRS ;` Thanks for your answer it looks suitable but above is my query where number of hours must be dynamically provided and hence i guess `+interval '1' hour` wont work. Any alternate for that — siva ramakrishna alla, Apr 15 '16 at 13:13
something like this if it is possible `interval LAST_X_HRS` if it is possible as I tried it is throwing error — siva ramakrishna alla, Apr 15 '16 at 13:22
You can do it in MySQL (see this [SQLFIDDLE](http://sqlfiddle.com/#!9/70b77/2)) but not in Oracle - you would need to use `NUMTODSINTERVAL()` to create a dynamic interval in Oracle. — MT0, Apr 15 '16 at 14:41

to_char equivalent that should work in both oracle and mysql

2 Answers2