Datasets that contain date-time columns can use them for filtering in the Query Tool. You can get an overview of other filters in this article. 

Date time filtering can be used alongside all our other IQL functions to filter the results of those queries within a certain time range. 

Date-time filtering syntax

Datetime filtering uses the There are two components available for your filter:

Time zone specification

Where clause with date-time

Time representations

Time zone specification

The platform understands time in UTC - so you can specify your time zone by specifying the zone using +/- hours from UTC or using the IANA Time Zone name 

For example:

Timezone: +2:00
Timezone: Australia/Sydney
Timezone: America/New_York

Where clause with date-time

You can specify the specific time bracket you are interested in. This behaves the same as other WHERE filters. 

To specify a bracket of time you can use WHERE and then one of the operators available. Please note we do not support the operator '=' with date time. For example:

• WHERE date column name BETWEEN 'start time' AND 'end time' 
• WHERE date column name > 'start time' AND date column name < 'end time'
• WHERE date column value <= 'date/time'

Examples:

The below query will count the results between Jan 1st 2024 midnight and midday:

SELECT COUNTO 
from AcmeAlTime 
WHERE AcmeAlTime. time24h/time24h > '2024-01-01 00:00:00' 
AND 
AcmeAlTime .time24h/time24h < '2024-01-01 12:00:00'

The below query will also count the results between Jan 1st 2024 midnight and midday:

SELECT COUNTO 
from AcmeAlTime 
WHERE AcmeAlTime.time24h/time24h 
BETWEEN 
'2024-01-01 00:00:00' 
AND 
'2024-01-01 12:00:00'

The below query will include all data from 180 days until now (approx the last 6 months).

It is specially useful when you have an automated query that is running every day for a specified lookback window (last week, last 3 months etc). By setting the filter in this way, you don’t need to change the date in the query every day. The query stays the same but always represents the last Xdays from today.

SELECT COUNT from AcmeAlTime WHERE AcmeAlTime.timeYear/timeYear > 'now-180đ'

Time representations

You can filter by any data point available in your date-time data. 

If you have  the depth of a full timestamp you can filter based on, for example:

• Year
• Year - Months
• Date
• Full specific timestamp 
• Lookback windows: e.g.   everything from 365 days ago to 180 days ago would be BETWEEN ‘now-365d’ AND ‘now-180d’

If you are stipulating two datapoints, these do not need to have the same representation as seen below: