Date functions
One field in the user profile can be used to store a date. The OpenEMM provides several special functions which use a date. The following example creates a target group for all recipients whose birthday is on a specific day.
1. | In the navigation bar, select Target groups, then New target group. To the right, the entry dialog for a new target group is displayed. Fill in the Name and Description. |
2. | Select the birthday field in the second drop-down list. Please note: This is not a standard field. You must create it yourself (and enter birthdays, of course). You will find more information on user-defined fields in chapter "Extending recipient's profiles". The relational operator should be the “equals” sign. |
3. | For the current date, the OpenEMM supports the special keyword now(). When processing the rule, the system replaces the keyword by the current date. Enter now() into the reference value entry field. Click on Add to close the rule. |
Fig. 6.3: The keyword now() represents the current date.
4. | A new search rule is displayed under Target group definition. Behind the reference value (now() in our example) you will see further drop-down lists for the date format . YYYYMMDD represents year, month, day. The system compares the whole date including the year. This is not very practical when looking for birthdays, because it is rather improbably that a new-born baby will already be in your database from birth with an email address. You do want to find people on their birthdays, however. You should therefore select the date format MMDD. This will cause the system to compare the month and day, but not the year. |
5. | Click on Save to close the field. |
In the above example a target group of recipients was defined whose birthdays are today. However it sometimes makes sense to create a target group with a comparative value that is not related to the current date but a point in the future or the past. To make this case clearer, there follows an example for a target group of all recipients who were created yesterday in OpenEMM:
1. | The date on which a recipient profile was created in OpenEMM is stored in the field creation_date. Select this field in the selection list as in the example above. As comparison operator, again use the equal to character. |
2. | You must know enter an expression that shows OpenEMM that it is not the current date that is being dealt with, but a date in the past, namely one day ago. You do this simply with the expression date_sub(now(), interval 1 day ), i.e. current date minus 1. This is again saved by clicking on the button Add. |
3. | The next step continues in the same way as for step 4 of the example above. |
Please note: If you want to create a target-group using a date that is further in the past than one day, you have to show it to the OpenEMM by using a slightly different keyword. E.g. two days in the past would be date_sub(now(), interval 2 day ), three days date_sub(now(), interval 3 day ) and so on. As you can see, this is a pattern, date_sub(now(), interval<n> day ) , where <n> has to be replaced by the number of days. If you are referring to a date that is exactly one day, one week, one month, or one year in the past, another pattern can be used. This is how looks: date_sub(now() , interval <n> <expr> ) with the following values to be uses: DAY, WEEK, MONTH, YEAR.
If you want to refer to a date in the future instead of one in the past, the keywords are quite similar. The only difference is that the syllable sub has to be changed to add. The keyword for tomorrow is for example date_add(now(), interval 1 day ), the expression for 10 days in the future is date_add(now(), interval 10 day ).
For further information please have a look at the MySQL manual (MySQL is the database OpenEMM uses).