Link to home
Start Free TrialLog in
Avatar of Ghost Freak
Ghost Freak

asked on

Customize date format in hsqldb

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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.