A date parameter in a data feed query can be set to

• a hard-coded value, for example, 2024-12-12
• a date expression calculated by date methods, discussed in this article

Date Expressions

A date expression is used to define a date/time, typically relative to the current date. This is useful when retrieving data for a moving time range, for example, when retrieving data from the start of the month to today, or from the previous week.

IMPORTANT: Date expressions must be entered in the Query URL, not as a Query Parameter, during data feed creation.

Date Format

This article makes several references to date format, which describes how a date is represented. Dates can be formatted in a variety of ways. For example, December 1, 2024 can be represented as:

• December 1, 2024 (MMMM d, yyyy)
• 01-12-2024 (dd-MM-yyyy)
• 1733011200 (Unix time format, the number of seconds that have elapsed since midnight January 1, 1970 UTC)

Syntax

A date expression is denoted by the keyword "date" followed by a chain of one or more date methods and contained within curly braces:

{date.setMethod.dateSettings.dateManipulationMethods.dateShortcut.formatMethod}

Date Method Descriptions

All methods are optional and must be used in the order listed in the following table. Date methods return in EST by default.

Examples

The following retrieves data from the first day of the current quarter at 00:00:00 EST (start-date) to the current date at 12:00:00 EST (end-date), both in the default yyyy-MM-dd format.

https://www.webservice.com/data? start-date={date.tz("cst").startOfQuarter}&end-date={date.set("12:00:00","HH:mm:ss").today}

The following retrieves data from the start of the previous year at current time GMT (start-date) to the current date/time GMT (end-date).

https://www.webservice.com/data? start-date={date.addYears(-1).startOfYear.format("unixtime")}&end-date={date.today.format("unixtime")}

Some web service APIs require date/times to be specified in Unix time as milliseconds; in these cases, append 000 to the unixtime parameter; for example:

https://www.webservice.com/data? start-date={date.add(-7).format('unixtime')}000&end-date={date.today.format("unixtime")}000&

The following retrieves data from the start of the fiscal year to today, where the fiscal year starts on April 1st. This example uses firstMOY.

https://www.webservice.com/data? start-date={date.firstMOY("apr").startOfYear.format()}&end-date={date.today}

The following retrieves data from the previous week where the first day of the week is Monday. This example uses firstDOW.

https://www.webservice.com/data? start-date={date.firstDOW("mon").addWeeks(-1).startOfWeek.format()}&end-date={date.firstDOW("mon").addWeeks(-1).endOfWeek.format()}

Common queries

You can copy these example queries and change the date parameters in green according to your data.

Google Ads

SELECT Amount, Period, Ctr, Clicks, ConversionRate, Cost, CostPerConversion, Date, Impressions, ConversionValue FROM CAMPAIGN_PERFORMANCE_REPORT DURING {date.addMonths(-1).format('yyyyMMdd')}, {date.today.format('yyyyMMdd')}

• DURING {date.add(-7).format('yyyyMMdd')}, {date.today.format('yyyyMMdd')} 
• DURING {date.addMonths(-1).startOfMonth.format('yyyyMMdd')}, {date.today.format('yyyyMMdd')} 

Google Analytics

Query URL:
https://analyticsdata.googleapis.com/v1beta/properties/{propertyID}:runReport
Query Body:
{
  "dimensions": [
    {
      "name": "date"
    },
    {
      "name": "defaultChannelGroup"
    },
    {
      "name": "eventName"
    },
    {
      "name": "pagePath"
    }
  ],
  "Metrics": [
    {
      "name": "conversions"
    },
    {
      "name": "engagementRate"
    },
    {
      "name": "sessions"
    },
    {
      "name": "totalUsers"
    },
    {
      "name": "userConversionRate"
    }
  ],
   "dateRanges": [
     {
       "startDate": "{date.last30days}",
       "endDate": "{date.today}"
     }
   ]
}

•     {

"startDate": "{date.addMonths(-3).format()}",

"endDate": "{date.today}"

}

•     {

"startDate": "{date.addYears(-1).startOfYear.format()}", 

"endDate": "{date.addYears(-1).endOfYear.format()}"

}

•     {

"startDate": "{date.addMonths(-3).startOfMonth.format()}", 

"endDate": "{date.today}"

}

Facebook

https://graph.facebook.com/v2.5/insights/page_views/day? since={date.add(-90).format()}&until={date.today}

• since={date.last30Days}&until={date.today}

Date Expression Validation

To validate the results returned by a date expression, create a REST/URL data feed in PowerMetrics (it is not necessary to save it).

1. Click the + button beside Data Feeds in the left navigation sidebar.
2. Click Select data.
3. On the Where is your data? page, under Core Services, select REST/URL.
4. On the Configure data service page, set Query URL to: 

https://httpbin.org/get?<fromDate>&<toDate>

where <fromDate> and <toDate> are the date parameters in your query.

For example, if the date parameters are:

from={date.addHours(-15).format("yyyy-MM-dd'T'HH:mm:ssZ")}

to={date.today.format()}

Set the Query URL to:

https://httpbin.org/get?from={date.addHours(-15).format("yyyy-MM-dd'T'HH:mm:ssZ")}&to={date.today.format()}

5. Set Data Format to JSON and set Method to GET.
6. Click Get data. The date results will be listed under the args object.
7. Click Cancel to exit.