Relative date
Searching on a date (e.g. a creation date or reservation date) is often used within filters. The search is often based on a specific date (e.g. all reservations for today). The disadvantage of this is that as a user you have to enter today's date every day to obtain the correct information from the system.
To make this easier, there is the option to use "relative data". Relative means "compared to". A relative date is a calculated date compared to today. To calculate the relative date, minutes, days, hours and weeks can be used, lying before or after today's date. When searching, there are two types of fields: date fields (such as the reservation from date) and date-time fields (such as the "Created" and "Updated" fields). With a date field, only days are compared. For date-time fields on day and time components. In concrete terms, a number of fixed values for date fields:
- -2d means "The day before yesterday"
- -1d means "Yesterday"
- 0d means "Today"
- 1d means "Tomorrow"
- 2d means "The day after tomorrow"
Example
Assuming a filter based on all reservations today.
A relative date would then be "started >= 0d" (counting today) and "started <= 0d" (counting today)
Assuming a filter based on all reservations for tomorrow.
A relative datum would then be >= 1d and <= 1d.
Assuming a filter based on all reservations for the 7 days, including today.
A relative date would then be 0d (including today) and 7d (7 days ahead from today's date)
Assuming a filter based on all reservations for the 4 weeks, excluding this week.
A relative date would then be 1w (shows reservations after 1 week from today's date) and 4w (up to 4 weeks ahead from today's date)
Assuming a filter based on new customers who signed up in the last 12 hours.
A relative date would then be -12h (12 hours back from today) and 0d (including today until 00:00 the next day)
If we want to provide this example via the simple search screen, we use the "MORE" button behind the relative date.
The table below shows the various codes that can be used to specify relative values. These are also described in the simple search screen.
| Code | Meaning |
|---|---|
| m | Minutes |
| h | Hour |
| d | Day |
| w | Week |
Whole calendar periods
With d, w, h, m and s you specify a distance: -30d means thirty days back from now. That window shifts along every day, so it is not the same as a calendar month. If you wanted "last month", -30d was never quite right.
For that there are four anchors. They do not express a distance but a fixed place in the calendar:
| Letter | Meaning |
|---|---|
b | first day of the month (begin) |
e | last day of the month (end) |
j | first day of the year (1 January) |
y | last day of the year (31 December, year) |
The number in front says how many months or years to shift. 0 is the current one, a minus sign goes back in time.
| From | To | Gives you |
|---|---|---|
0b | 0e | this month, from the 1st up to and including the last day |
-1b | -1e | last month |
1b | 1e | next month |
0j | 0y | this year |
-1j | -1y | last year |
1j | 1y | next year |
The date filter has a Whole calendar period dropdown for this. Pick Last month and the From and to fields are filled with -1b and -1e. So you see the matching syntax straight away and can still adjust it by hand.
Why month and year always need a letter
m means minutes, not months. So -1m is one minute ago. There is deliberately no bare month unit: months and years are always addressed through an anchor, so through b, e, j or y. Type 1y without meaning an anchor and you get an error rather than a wrong date.
Anchors are exact, including the time
A begin anchor sits at 00:00:00 and an end anchor at 23:59:59, regardless of when you run the search. Durations behave differently: -30d at 14:00 produces a date with 14:00 as its time, so on a column that carries a time you miss that morning. Anchors do not have that problem.
Anchors also always land on real month boundaries. Run a filter on 31 August and -1b is simply 1 July, -1e is 31 July. And 0e in February is the 28th or the 29th, depending on the leap year.
An anchor stands alone
Do not combine an anchor with another unit. -1b is fine, -1b 2d is not and returns an error. For a period that does not sit on a month boundary, use the durations instead.
