Knowledge Center

Knowledge Center > API > Export ZOQL > Export ZOQL Dates and Datetimes

Export ZOQL Dates and Datetimes

Zuora stores all dates as timestamps, but using timestamps in a query can be difficult - for example, setting the start time to be 00:00:00-08:00 and the ending time to be 23:59:59-08:00. For this reason, Export ZOQL has several features designed to simplify the use of dates in queries.

Export ZOQL is used for creating exports from Zuora data sources, and uses a different syntax than standard ZOQL. These special date/time features are not supported in standard ZOQL.

Comparing Dates with Datetimes

Typically, when you filter by a datetime you must have an exact match for the query to return a value. For example:

select * from Invoice where CreatedDate = '2011-02-01T08:52:13.087'

This will return only the invoices created at exactly the date and time specified, including the millisecond. In most cases you will be looking for objects with datetimes within a range of values. You could use the following query to see all of the invoices created on 2011-02-01:

select * from Invoice where CreatedDate >= '2011-02-01T00:00:00.000' and CreatedDate < '2011-02-02T00:00:00.000'

Although this query works, it is complex and difficult to use. To make this type of query easier, Export ZOQL provides allows you to specify the date without the time:

select * from invoice where createddate = '2011-02-01'

If you specify only a date, Export ZOQL interprets it to include any time within the 24-hour period specified by that date. If you have multiple invoices created at different times on 2011-02-01, this query will find all of them. Similar rules apply when using the other comparison operators. For example, the following query returns every invoice that was created on or after 2011-02-02:

select * from invoice where createddate > '2011-02-01'

Using Datetime Functions

Simplify fields and values recorded as datetime timestamps with any of the following datetime functions:

  • DATE() - interprets a datetime value as a numerically expressed date without hour, minutes, seconds, or milliseconds.
  • YEAR() - returns only the year value of a timestamp. Example: YEAR(2011-02-01T08:52:13.087) returns 2011
  • MONTH() - returns only the numeric value of the month from a timestamp. Example: MONTH(CreatedDate) returns 2 if the CreatedDate timestamp value in a record was 2011-02-01T08:52:13.087
  • WEEK() - returns only the numeric value of the week (values 0-53 from a timestamp depending on the year and the date). Example: WEEK(2015-01-01T00:00:01.099) returns 0 and in the year 2015 WEEK(2015-01-04T00:01:01.009) returns 1 because after Saturday midnight (formally: Sunday) the new week begins.
  • QUARTER() - makes a standard interpretation of a datetime value as a numeric quarter value (1-4).  This function defines quarters by a traditional three-month calendar quarters starting on January 1, April 1, July 1, and October 1. Offset quarters are not supported. 
select * from invoice where YEAR(createddate) = '2015' AND QUARTER(createddate) = 2

Relative Date Expressions

Relative date expressions are an alternative way to specify dates and times. You can specify a time relative to now, or a date relative to today. The general form of a relative date expression is:

now/today +/- quantity unit-of-time (timezone)

The timezone is optional.

You can specify the following units of time:

  • millisecond
  • second
  • minute
  • hour
  • day
  • week
  • month
  • year

Now and Today

Use the now keyword to specify the current timestamp. Use the today keyword to specify the current day.

Now

Use the now keyword to specify the current timestamp. Any expression starting with now is a datetime, and will only match other datetimes exactly.

The following query will return all invoices equal to the current timestamp minus 24 hours. For example, if the current time is 6 PM on January 15, now - 1 day will return all invoices generated at 6 PM on January 14.

SELECT * FROM invoice WHERE createddate = 'now - 1 day'

To show all invoices generated in the last 24 hours, use >='now - 1 day'. For example:

SELECT * FROM invoice WHERE createddate >= 'now - 1 day'

The following query returns all invoices created yesterday, at any time of the day:

select * from invoice where createddate = 'today - 1 day'

Today

The today keyword specifies the current day. An expression starting with today is a date, and will match a range of datetimes.

The following query will return all invoices from the previous calendar day, regardless of the time stamp (in other words, all invoices from 00:00 to 23:59).

SELECT * FROM invoice WHERE createddate = 'today - 1 day'

Use the following query to find all of the invoices created earlier than 30 days ago:

select * from invoice where createddate < 'today - 30 days'

The following query returns all of the invoices created within the last 24 hours, some of which will be today and some of which will be yesterday:

select * from invoice where createddate = 'now - 1 day'

If daylight savings started or ended within the previous day, this could include the previous 23 or 25 hours, respectively, depending on the time zone.

Offsets

Use this to specify a time period with an offset.

SELECT Invoice.InvoiceNumber, Account.Name FROM Invoice WHERE createddate < 'today - 30 days' #Query for the last 30 days of data
SELECT * FROM invoice WHERE duedate >= 'today' AND duedate < 'today + 1 week' #Query for invoices whose due dates are due within the coming week.

Days Ago

Use days ago to select the last n days, setting the start and time to encompass the entire days.

SELECT AccountNumber, Balance, BillToContact.Firstname, BillToContact.Lastname, BillToContact.Homephone from Account WHERE Balance > 0 and BillToContact.Updateddate >= 'today - 30 days'

Time Zones

The value of a datetime or relative date expression will change if the time zone changes. For example, 02-01-2011T09:00:00 is a different time depending on whether you are in New York or in San Francisco. Similarly, today is a different range of datetimes, because midnight comes at different times depending on where you are in the world.

Because of this, you must consider time zones when writing Export ZOQL queries.

If you do not specify a time zone offset, the query will use the default time zone. For most users this will be Pacific Time. If you are located in a different time zone, but typically send dates to Zuora using Pacific Time (the default for our UI and SOAP APIs), then you should have no problem using this time zone. However, if you are located in a different time zone and store your dates and datetimes with Zuora using your local time zone, you must make sure to specify that time zone.

For dates and datetimes in the ISO 8601 format, you must specify a GMT offset. For relative date expressions, you can do the same by including it in parentheses after the expression:

select * from invoice where createddate < 'today - 30 days (-08:00)'

However, because relative date expressions can span daylight savings start and end dates, you can encounter a case where the GMT offset changes within the relative range of the expression. To solve this problem, you have the option of specifying a named time zone. These names are taken from the international time zone database, the standard used by most computer systems around the world. See the list of time zone names for more information.

The following query is uses the previous example, modified to use the Pacific Time zone:

select * from invoice where createddate < 'today - 30 days (America/Los_Angeles)'

Specifying City Names

We also support a format that allows you to specify the city name (using spaces instead of underscores):

select * from invoice where createddate < 'today - 30 days (Los Angeles)'

This automatically adjusts the GMT offset based on daylight savings time. 

Last modified
11:17, 3 Mar 2016

Tags

Classifications

(not set)