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

Note: This article mentions variables. Please note that variables are only applicable to Klips.

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("unixtime") or format("epochTime")

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 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

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

Click Data Sources in the left navigation sidebar.

If you don’t see the above option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources instead.

Click Create a New Data Source.
Under Additional options, select REST/URL.
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()}

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

Learn more

How can we help?

Date parameters in data source queries

Date Expressions

Date Format

Syntax

Date Method Descriptions

Examples

Common queries

Date Expression Validation

Learn more