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.
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'
This will return only the invoices created at exactly the date and time specified. 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' and CreatedDate < '2011-02-02T00:00:00'
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 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'
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 (time zone)
time zone is optional.
You can specify the following units of time:
Now and Today
now keyword to specify the current timestamp. Use the
today keyword to specify the current day.
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 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.
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 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'
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 time zone that a query uses will differ in the WSDL version you use. If you are using WSDL 69 or later, this will be the time zone your tenant uses. If you are using earlier WSDL versions, the query will use Zuora system time zone. See Before Changing Your Time Zone and DateTime Field Behavior After Changing Your Time Zone for more information.
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 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.
In Export ZOQL queries, you can use the following functions to extract information about datetimes:
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:
MONTH() -returns only the numeric value of the month from a timestamp. Example:
CreatedDatetimestamp value in a record was
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 YEAR(CreatedDate) from Invoice
select SUM(Amount),QUARTER(CreatedDate) from Invoice group by QUARTER(CreatedDate)
select SUM(UnitPrice) from InvoiceItem group by SKU,CreatedDate having YEAR(CreatedDate)=2017
You cannot use datetime functions in