0

I'm trying to make a timestamp comparison after parsing a couple strings that have the default "dow mon dd hh:mm:ss zzz yyyy" in java, using MyBatisPlus for a postgresql DB.

I'm updating legacy code so I'm not able to change a lot of the structure, so that's a constraint since it's using QueryWrapper to execute all queries. I've tried to read the documentation, but I mostly find references in chinese language which don't get translated properly.

What I have tried so far is the following:

QueryWrapper<SomeClassWithBaseMapper> result = new QueryWrapper();
        result.eq("firstcolumn", firstValueToCheck);
        if(dateStrFilter != null) {
            try{
                //PostgreSQL Syntax
                 String dateConversionEq = "(TO_TIMESTAMP(SUBSTRING(" + START_TIMESTAMP +
                  ",5), 'Mon DD HH24:MI:SS xxx YYYY')+(INTERVAL'10 MINUTES')) >= (TO_TIMESTAMP(SUBSTRING({0},5), " +
                  "'Mon DD HH24:MI:SS xxx YYYY'))";
                result.apply(dateConversionEq, dateStrFilter);

            } catch(Exception e){
                System.out.println(e);
            }
        }

I use substring to remove the starting name of the day (since I don't see any matching format pattern in postgresql docs). I attempt to use +(INTERVAL'10 MINUTES') basically to check whether the given date is at most 10 minutes old. But I get this error:

expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND"

If I remove this inline interval addition, the query works, but obviously I need results that are recent (in the last 10 minutes). Any suggestion to make this work?

Thanks!

Carlos S. Na
  • 63
  • 1
  • 2
  • 7
  • what's `xxx` in `...SS xxx YYYY`? – richyen Sep 13 '22 at 22:28
  • and what do you get when you do `System.out.println(dateConversionEq)`? – richyen Sep 13 '22 at 22:30
  • I put xxx just as a placeholder to skip the timezone. It seems to work fine. The dateConversionEq value is: WHERE firstcolumn = ? AND (TO_TIMESTAMP(SUBSTRING(start_timestamp,5), 'Mon DD HH24:MI:SS xxx YYYY')) >= (TO_TIMESTAMP(SUBSTRING(?,5), 'Mon DD HH24:MI:SS xxx YYYY')) An actual date string example is: "Wed Sep 14 10:38:07 CDT 2022" – Carlos S. Na Sep 14 '22 at 15:40
  • Seems to be an issue with Java or MyBatis? https://stackoverflow.com/questions/60861099/h2-failing-to-insert-data – richyen Sep 14 '22 at 16:16

0 Answers0