Customize date format in hsqldb

Ghost Freak
Ghost Freak used Ask the Experts™
on
Hi,
Im currently using psql but for the purpose of unit testing Im using HSQLDB to run my queries. All the queries works fine in psql but I get invalid datetime format exception in hsql.
Below is my query,
INSERT INTO microbatch_redrive
SELECT mii.*,(to_char(CURRENT_TIMESTAMP ,'yyyy-mm-ddThh:mi:ss.msZ')) FROM
(SELECT * FROM
  ( SELECT mi.*,me.extractid FROM
    (SELECT * FROM microbatch_info WHERE
        ((numattempts<= ${maxNumOfAttempts})
        AND (starttime>= '${starttime}') 
        AND (starttime<= '${endtime}')))
    AS mi LEFT OUTER JOIN microbatch_extract_info AS me ON mi.microbatchid = me.microbatchid) 
    AS mime WHERE((mime.raverunstatus='SUCCEEDED' AND mime.extractid is null)
    OR (mime.starttime < to_char(CURRENT_TIMESTAMP,'yyyy-mm-ddThh:mi:ss.msZ') AND mime.raverunstatus='NOT_STARTED')
    OR (mime.raverunstatus = 'FAILED')
    OR (mime.raverunstatus='STARTED' AND mime.starttime< (to_char(CURRENT_TIMESTAMP - INTERVAL '1' hour * ${extractTimeIntervalInHour},'yyyy-mm-ddThh:mi:ss.msZ')))
    )
) AS mii LEFT OUTER JOIN
(SELECT microbatchid FROM microbatch_redrive WHERE raverunstatus='REDRIVE_SUCCEEDED') AS mr ON mii.microbatchid=mr.microbatchid WHERE mr.microbatchid IS NULL;

Open in new window


ERROR:
 Testcase: getFailedMicrobatchesToRedrive(com.amazon.eradar.scheduledjobs.utils.RedriveFailedMicrobatchUtilTest):    Caused an ERROR
    [junit] data exception: invalid datetime format: Thh:mi:ss.msZ
    [junit] java.sql.SQLDataException: data exception: invalid datetime format: Thh:mi:ss.msZ
    [junit]     at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    [junit]     at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    [junit]     at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    [junit]     at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    [junit]     at com.amazon.eradar.common.util.RedshiftQueryExecutionUtil.executeOneWay(RedshiftQueryExecutionUtil.java:36)
    [junit]     at com.amazon.eradar.scheduledjobs.utils.RedriveFailedMicrobatchUtil.loadFailedMicrobatches(RedriveFailedMicrobatchUtil.java:52)
    [junit]     at com.amazon.eradar.scheduledjobs.utils.RedriveFailedMicrobatchUtil.getFailedMicrobatchesToRedrive(RedriveFailedMicrobatchUtil.java:36)
    [junit]     at com.amazon.eradar.scheduledjobs.utils.RedriveFailedMicrobatchUtilTest.getFailedMicrobatchesToRedrive(RedriveFailedMicrobatchUtilTest.java:161)


NOTE:-
I want the date to be in the format of  yyyy-mm-ddThh-mi-ss-msZ (Ex.,"2018-04-05T00:00:00.000Z")


Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial