2013R2 date-based target group date arithmetic in quotes

Use this forum to report bugs and to check for bugfixes and new releases of OpenEMM

Moderator: moderator

johanntagle
Posts: 15
Joined: Mon Jul 21, 2014 5:58 am

2013R2 date-based target group date arithmetic in quotes

Post by johanntagle »

First time for me to create new date-based target group ever since upgrading from 2013 to 2013R2. It looks like it now encloses any filter clause in quotes, causing it to be treated as a string. I installed a new openemm instance to make sure it's not just happening to my live machine. I created a simple change_date = current_timestamp target definition, and here's what is stored in the database:

Code: Select all

mysql> select * from dyn_target_tbl\G
*************************** 1. row ***************************
            target_id: 1
           company_id: 1
     target_shortname: Name
   target_description: Description (optional)
           target_sql: ( date_format(cust.CHANGE_DATE, '%Y%m%d') ='current_timestamp' )
target_representation: �� sr 0org.agnitas.target.impl.TargetRepresentationImpl���c�� LallNodest Ljava/util/ArrayList;xpsr java.util.ArrayListx����a� I sizexp   w   sr &org.agnitas.target.impl.TargetNodeDatchainOperatorZ closeBracketAfterZ openBracketBeforeI primaryOperatorL 
dateFormatt Ljava/lang/String;L primaryFieldq ~ L primaryFieldTypeq ~ L primaryValueq ~ xp         tyyyymmddt CHANGE_DATEt DATEt current_timestampx
              deleted: 0
          change_date: 2014-09-24 04:07:14
        creation_date: 2014-09-24 04:07:12
Obviously date_format(cust.CHANGE_DATE, '%Y%m%d') ='current_timestamp' won't match anything. For now I work around this by manually modifying the target_sql value in the database.
johanntagle
Posts: 15
Joined: Mon Jul 21, 2014 5:58 am

Re: 2013R2 date-based target group date arithmetic in quotes

Post by johanntagle »

Btw system info:

Operating System: Ubuntu 12.04.5 LTS

$ java -version
java version "1.7.0_65"
Java(TM) SE Runtime Environment (build 1.7.0_67-b01)
Java HotSpot(TM) 64-Bit Server VM (build 24.65-b04, mixed mode)

Tomcat 6.041
maschoff
Site Admin
Posts: 2596
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: 2013R2 date-based target group date arithmetic in quotes

Post by maschoff »

Please try "now()" instead of "timestamp". This works for me.
OpenEMM Maintainer
johanntagle
Posts: 15
Joined: Mon Jul 21, 2014 5:58 am

Re: 2013R2 date-based target group date arithmetic in quotes

Post by johanntagle »

Ok thanks maschoff, that worked. Would like to take this opportunity though to reiterate my post at https://forum.openemm.org/development-f ... html#p9023 - I mentioned there the "wish" to have an option to not use date_format() for a date-based target group definition and allow the user to define the exact target_sql he would like to use. Some SQL knowledge is needed anyway to come up with "now() - interval 7 day" to create offset dates, so why not allow a user to fully define the SQL filter clause. Right now I manually modify the target_sql value to use something like ">= curdate(), interval 7 day" and ">= curdate(), interval 12 months" to filter by date of last activity. Yes I know this will get overwritten if someone touches the target group using the web interface, but I find the performance benefits to be worth it.
anosh123
Posts: 1
Joined: Mon Mar 30, 2015 8:05 am

Re: 2013R2 date-based target group date arithmetic in quotes

Post by anosh123 »

You can not use mailinglist IDs of maillists being deleted, because the mailinglist ID has to be unique and the mailinglist ID of a deleted mailing list has already been used.
anosh g
Post Reply