Date-type attributes
Date attributes support specific operators and conditions.
Date operators act on complete 24-hour days and default to a shop's time zone.
For example, a New York shop (GMT-4) with the following query would include customers who last ordered between midnight (T00:00:00) on May 30th, 2020 to midnight on May 31st, 2020 in the shop's timezone.
In SQL, with UTC-based times, this would look like the following:
The following segment query would include customers who last ordered from midnight on May 31st:
The following are supported operators for date-type attributes.
| Operators | Description | Examples |
|---|---|---|
= |
Equal-to | last_order_date = 2020-05-30 |
!= |
Not-equal-to | last_order_date != 2020-05-30 |
> |
After | last_order_date > 2020-05-30 |
>= |
On or after | last_order_date >= 2020-05-30 |
< |
Before | last_order_date < 2020-05-30 |
<= |
On or before | last_order_date <= 2020-05-30 |
BETWEEN {condition1} AND {condition2} |
Equivalent to condition1 <= n <= condition2 |
BETWEEN 2020-02-30 AND 2020-03-30 |
Date-type attributes accept specific conditions.
{absoluteDate}
Anchor link to section titled "{absoluteDate}"Language-sensitive formatted dates are not accepted.
| Format | Examples |
|---|---|
yyyy-mm-dd |
Represents an absolute date without time |
{dateOffset}
Anchor link to section titled "{dateOffset}"Syntactic sugar for an absolute date without time. For example, if the date is May 15th 2021, then -7d is syntactic sugar for 2021-05-07.
The following are examples of {dateOffset}:
| Format | Description | Examples |
|---|---|---|
{+/-} {#} d |
Number of days | last_order_date > -7d |
{+/-} {#} w |
Number of weeks | last_order_date >= -2w |
{+/-} {#} m |
Number of months | last_order_date = -1m |
{+/-} {#} y |
Number of years | last_order_date < -1y |
{namedDate}
Anchor link to section titled "{namedDate}"Syntactic sugar for an absolute date without time.
{namedDate} supports the following operators:
When using the BETWEEN operator, condition types can be mixed.
| Format | Description | Examples |
|---|---|---|
today |
Date when the query is run or segment is queried | last_order_date = today last_order_date > today last_order_date < today |
yesterday |
Previous day | last_order_date = yesterday last_order_date < yesterday |
12_months_ago |
12 months ago | last_order_date > 12_months_ago |
90_days_ago |
90 days ago | last_order_date > 90_days_ago |
30_days_ago |
30 days ago | last_order_date > 30_days_ago |
7_days_ago |
7 days ago | last_order_date > 7_days_ago last_order_date = 7_days_ago |
{absoluteDateTime}
Anchor link to section titled "{absoluteDateTime}"In some cases, merchants might want finer segment granularity, such as the segment of customers that participated in a timed flash sale. The segmentation query syntax supports an absolute datetime in place of an absolute date.
| Format | Description | Examples |
|---|---|---|
yyyy-mm-ddTHH:mm:ss |
Represents an absolute date with time in the shop's timezone | last_order_date > 2020-05-30T16:00:00 |
Times default to a shop's timezone. For example, a New York shop (GMT-4) with the following query would include customers means last_order_date > 2020-05-30T20:00:00 in UTC.
Only 24h format is supported. For example,
HHneeds to be be <= 23 (24h =00:00:00).Leading zeros are required, for example,
23:00:00.Time-only syntax is not supported. For example, the following syntax is invalid:
You can't extend
{namedDate}or{dateOffset}with a time-specifier.