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,
ERROR:
Testcase: getFailedMicrobatchesToRed rive(com.a mazon.erad ar.schedul edjobs.uti ls.Redrive FailedMicr obatchUtil Test): 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.s qlExceptio n(Unknown Source)
[junit] at org.hsqldb.jdbc.JDBCUtil.s qlExceptio n(Unknown Source)
[junit] at org.hsqldb.jdbc.JDBCStatem ent.fetchR esult(Unkn own Source)
[junit] at org.hsqldb.jdbc.JDBCStatem ent.execut e(Unknown Source)
[junit] at com.amazon.eradar.common.u til.Redshi ftQueryExe cutionUtil .executeOn eWay(Redsh iftQueryEx ecutionUti l.java:36)
[junit] at com.amazon.eradar.schedule djobs.util s.RedriveF ailedMicro batchUtil. loadFailed Microbatch es(Redrive FailedMicr obatchUtil .java:52)
[junit] at com.amazon.eradar.schedule djobs.util s.RedriveF ailedMicro batchUtil. getFailedM icrobatche sToRedrive (RedriveFa iledMicrob atchUtil.j ava:36)
[junit] at com.amazon.eradar.schedule djobs.util s.RedriveF ailedMicro batchUtilT est.getFai ledMicroba tchesToRed rive(Redri veFailedMi crobatchUt ilTest.jav a: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
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;
ERROR:
Testcase: getFailedMicrobatchesToRed
[junit] data exception: invalid datetime format: Thh:mi:ss.msZ
[junit] java.sql.SQLDataException:
[junit] at org.hsqldb.jdbc.JDBCUtil.s
[junit] at org.hsqldb.jdbc.JDBCUtil.s
[junit] at org.hsqldb.jdbc.JDBCStatem
[junit] at org.hsqldb.jdbc.JDBCStatem
[junit] at com.amazon.eradar.common.u
[junit] at com.amazon.eradar.schedule
[junit] at com.amazon.eradar.schedule
[junit] at com.amazon.eradar.schedule
NOTE:-
I want the date to be in the format of yyyy-mm-ddThh-mi-ss-msZ (Ex.,"2018-04-05T00:00:00.
Thanks in advance
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers 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.