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

• a hard-coded value, for example, 2024-12-12
• a variable, for example, {props.dateVar}
• 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 source creation.
• Date expressions can contain variables using the syntax: props.varName.

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)

For further information, see Date Formats.

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()} 

The following are examples using the timeZone variable set to the time zone CST. The syntax for including variables in date expressions is: props. timeZone .

https://www.webservice.com/data? startDate={date.tz(props. timeZone ).add(props.days).format()} 

https://www.webservice.com/data? startDate={date.tz(props. timeZone ).startOfMonth.format(props.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.addMonths(-3).format()}",
    "endDate": "{date.today}"
    }
  ]
    

  "dateRanges": [  
  {
    "startDate": "{date.addYears(-1).startOfYear.format()}",
    ""endDate": "{date.addYears(-1).endOfYear.format()}"
    }
  ]
  

  "dateRanges": [  
  {
    "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 source in Klipfolio (it is not necessary to save it).

1. Click Data Sources in the left navigation sidebar.
   If you don’t see this option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.
2. On the data source list page, click Create a New Data Source.
3. Under Additional options, select REST/URL.
4. 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. 

Learn more

• Variables
• Date Formats
• Guide to REST API data sources