Date parameters in data source queries

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

  • a hard-coded value, for example, 2015-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, 2015 can be represented as:

  • December 1, 2015 (MMMM d, yyyy)
  • 01-12-2015 (dd-MM-yyyy)
  • 1448928000 (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.

METHOD DESCRIPTION EXAMPLE

date

Mandatory keyword. Sets the date/time to current date/time.  date

setMethods are used to set the date/time. A date expression can contain zero or one setMethod.

set(dateString)

Sets the date/time to the value specified by dateString where dateString uses yyyy-MM-dd format. Time is set to 00:00:00 EST.  set("2016-01-31")

set(dateString, dateFormat)

Sets the date to the value specified by dateString in the format specified by dateFormat.  set("310116","ddMMyy")
 start() Equivalent to set("00:00:00","hh:mm:ss").  start()
 end() Equivalent to set("23:59:59","hh:mm:ss").  end()

dateSettings are used to define settings for time zone, first day of week and first month of year. A date expression can contain zero or more dateSettings.

tz(tzCode) Set the date/time to the specified time zone.  tz("est")
firstDOW(dayOfWeek)

Set the first day of the week to one of sun, mon,tue, wed, thu, fri, sat.

Used with startOfWeek and endOfWeek methods.

When this method is not used, the default first day of the week is Sunday.

 firstDOW("mon")
 firstMOY(month) Set the first month of the year to one of jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec.

Used with startOfYear and endOfYear methods.

When this method is not used, the default first month of the year is January.

 firstMOY("jan")

dateManipulationMethods are used to modify the date expression by adding or subtracting units of time. A date expression can contain zero or more dateManipulationMethods.

add(integer) Add n days to the current date/time. Use negative numbers to subtract days.

add(-7)

add(14)

addMinutes(integer) Add n minutes to the current date/time. Use negative numbers to subtract minutes.

addMinutes(30)

addMinutes(-15)

addHours(integer) Add n hours to the current date/time. Use negative numbers to subtract hours.

addHours(-1)

addHours(5)

addWeeks(integer) Add n weeks to the current date/time. Use negative numbers to subtract week.

addWeeks(4)

addWeeks(-26)

addMonths(integer) Add n months to the current date/time. Use negative numbers to subtract months.

addMonths(-1)

addMonths(5)

addQuarters(integer) Add n quarters to the current date/time. Use negative numbers to subtract quarters.

addQuarters(1)

addQuarters(-3)

addYears(integer) Add n years to the current date/time. Use negative numbers to subtract years.

addYears(-2)

addYears(1)

dateShortcuts set date/time in yyyy-MM-dd format. A date expression can contain zero or one dateShortcuts

today Set the date/time to the current date/time EST. today
yesterday Set the date/time to yesterday, current time EST. yesterday
last7Days Set the date/time to 7 days ago, the current time EST. last7Days
last30Days Set the date/time to 30 days ago/current time EST. last30Days
last60Days Set the date/time to 60 days ago/current time EST. last60Days
startOfWeek Set the date/time to the start of the current week/current time EST, where the start of the week is specified by firstDOW. startOfWeek
endOfWeek Set the date/time to the end of the current week/current time EST, where the start of the week is specified by firstDOW. endOfWeek
startOfMonth Set the date/time to the start of the current month/current time EST, where the start of the month is specified by firstMOY. startOfMonth
endOfMonth Set the date/time to the end of the current month/current time EST, where the start of the month is specified by firstMOY. endOfMonth
startOfQuarter Set the date/time to the start of the current quarter/current time EST, where the start of the quarter is specified by firstMOY. startOfQuarter
endOfQuarter Set the date/time to the end of the current quarter/current time EST, where the start of the quarter is specified by firstMOY. endOfQuarter
startOfYear Set the date/time to the start of the current year/current time EST, where the start of the year is specified by firstMOY. startOfYear
endOfYear Set the date/time to the end of the current year/current time EST, where the start of the year is specified by firstMOY. endOfYear

formatMethods are required when either

  • a dateShortcut is not used in the date expression, or
  • a dateShortcut is used in the date expression and the required date format is different than yyyy-MM-dd (by default dateShortcuts return yyyy-MM-dd format)

A date expression can contain zero or one formatMethods.

format() Format the date in yyyy-MM-dd format format()
format(dateformat) Format the date in the specified format.

format("ddMMyyyy")

format("epochTime")

Note: epochTime is the date parameter representation for Unix time format.

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), both in Unix time format represented with the parameter: epochTime.

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

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

https://www.webservice.com/data?start-date={date.add(-7).format('epochTime')}000&end-date={date.today.format("epochTime")}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 Adwords

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

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:<Your-Profile-ID>&dimensions=ga:country,ga:date&metrics=ga:sessions&start-date={date.last30Days}&end-date={date.today}

  • start-date={date.addMonths(-3).format()}&end-date={date.today}
  • start-date={date.addMonths(-3).startOfMonth.format()}&end-date={date.today}
  • start-date={date.addYears(-1).startOfYear.format()}&end-date={date.addYears(-1).endOfYear.format()}

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. Go to the Data Sources library, click Create a New Data Source and select the REST/URL Core Connector.
  2. 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()}

  1. Set Data Format to JSON and set Method to GET.
  2. Click Get Resource. The date results will be listed under the args object.
  3. Click Cancel to exit. 

Learn more