Skip to main content
Interania

Time query syntax reference

0votes
14updates
436views
This applies tov2.22

When working in the Interana Explorer and dashboard view, you can select a time range for your query. Interana supports both literal time values and relative time values. This document provides context about time ranges, timezones, and natural language we support for relative times.

Examples are provided for a user whose timezone is Pacific Standard Time (PST). Times are displayed in 24-hour format to be unambiguous.

Specific dates and times

You can specify query time boundaries using exact dates, exact times, or combinations of date and time.

Dates

You can specify dates using any of several formats, with or without time components. If you do not specify a time component, Interana uses midnight, or the zeroth millisecond of the specified calendar date.

Input Result for a user in PST Notes

01/05/2015

1/5/15

15/1/5

1.5.2015

Jan 05 2015 00:00:00 GMT-0800 (PST)

MM/DD/YYYY

MM/DD/YY

YY/MM/DD

MM.DD.YYYY

  • You can omit leading zeros
  • Interana automatically interprets two-digit years greater than 28 as 19XX

Jan 5 2015

2015 Jan 5

5 Jan 2015

5.Jan.2015

Jan 05 2014 00:00:00 GMT-0800 (PST) Providing month names allows for more flexibility in order

Times

You can specify times up to second precision using the format HH:MM:SS; however the minute and second components are optional. 

Interana supports 12- and 24-hour formats. We recommend that you always specify "am" or "pm" when using the 12-hour format, and be sure to include colons when using the 24-hour format to prevent parsing as milliseconds (see Milliseconds since the Unix Epoch for more information).

If you include a date with the time, the date component must precede the time component. If you do not specify a date, Interana uses the current date.

Input Result for PST user on 01/01/2015 Notes

1/1/15 2:20pm

1/1/15 14:20

Jan 01 2015 14:20:00 GMT-0800 (PST) 12- or 24-hour time. If a time can be parsed as 24-hour time, Interana ignores its AM/PM component.

0:00

12 am

12am

12:00 am

12:00:00 am

0:00

12 am

12am

12:00 am

12:00:00 am

Jan 01 2015 00:00:00 GMT-0800 (PST) Midnight, in the user's PST timezone

12:00

12 pm

12pm

12:00 pm

12:00:00 pm

Jan 01 2015 12:00:00 GMT-0800 (PST) Noon, in the user's PST timezone

0

12

15

24

Dec 31 1969 16:00:00 GMT-0800 (PST) Integers are interpreted as milliseconds. See Milliseconds since the Unix Epoch for more information.
2:20pm 1/1/15 error When specifying both date and time, the date component must precede the time component
noon error Not supported
midnight error Not supported

Milliseconds since the Unix Epoch

If you input only an integer number, Interana interprets it as the number of milliseconds relative to January 1, 1970 00:00:00 UTC.

Input Result for a user in PST Notes
0 Dec 31 1969 16:00:00 GMT-0800 (PST) Jan 1, 1970 00:00:00 UTC, offset -8 hours due to the user's PST timezone
28800000 Jan 01 1970 00:00:00 GMT-0800 (PST) 8 hours in milliseconds (Jan 1, 1970 08:00:00 UTC), or Jan 1, 1970 00:00:00 in the user's PST timezone
-28800000 Dec 31 1969 08:00:00 GMT-0800 (PST) -8 hours in milliseconds (Dec 31 1969 16:00:00 UTC), offset an additional -8 hours due to the user's PST timezone

Relative time syntax

Interana also supports relative time syntax. These terms are "sticky," meaning that a query using them will roll forward in real time (versus a query based on literal time values whose results will not change with the passage of time).

The general format for this type of input is "<number> <unit> ago"

For example: 7 days ago

Supported numbers

Numbers can be integers or decimals. Interana ignores the sign of numbers used this way.

Use the numeric form and not the spelled out form of the number. For example, you can use 7 days ago but not seven days ago.

Supported units

Interana supports the following units of time:

  • second(s)
  • minute(s)
  • hour(s)
  • day(s)

The following units align to calendar days, meaning that the time component is set to midnight in the user's timezone:

  • week(s)
  • year(s)

We support both precise and calendar-aligned relative time:

Precise (XX:xx:xx AM/PM) Start-of-Day Aligned (12:00:00 AM)
now today
1 day ago yesterday
1 week ago last week
10 days ago last 10 days
1 month ago last month
1 year ago last year

note_icon.png Avoid cross-referencing across these columns (for example, don't use "yesterday" to "now"). This will produce time ranges that are +/- hours on either side which is unexpected behavior for most users.


 

Input  Result for a user in PST (GMT-0800) on 01/01/2015 at 1:15 PM Notes

1 hour ago

1 hour before

Jan 1 2015 12:15:00 GMT-0800 (PST) Exactly 1 hour ago in the user's timezone
last week Dec 25 2014 00:00:00 GMT-0800 (PST)

Exactly one week before the current time, aligned to the calendar day in the user's timezone.

7 days ago Dec 25 2014 13:15:00 GMT-0800 (PST)

Exactly 7 days ago aligned to the current time in the user's timezone

last 7 days

7 days before

Dec 25 2014 00:00:00 GMT-0800 (PST) One week ago, aligned to the start of the calendar day in the user's timezone

1 day ago

24 hours ago

1440 minutes ago

86400 seconds ago

Dec 31 2014 13:15:00 GMT-0800 (PST)

1 day ago aligned to the current time in the user's timezone

1 week ago

1 week before

Dec 25 2014 13:15:00 GMT-0800 (PST)

This works for the time Start value and when comparing time periods. This is the default value for time comparisons.

If you set the Time Window and Resolution of a query to 1 week, 1 week ago will end at the next Sunday in the time period.

1 day before Dec 31 2014 00:00:00 GMT-0800 (PST) This works for the time Start value and when comparing time periods
one week ago error Not supported
1 week ago 5pm error Not supported

Today, yesterday, and now

Interana also supports the today, yesterday, and now keywords. Note that today and yesterday align to calendar days, and now uses the current time in the user's timezone.

Input Result for a user in PST (GMT-0800) on 01/01/2015 at 1:15 PM Notes
now Jan 01 2015 13:15:00 GMT-0800 (PST) The current date and time in the user's timezone
today Jan 01 2015 00:00:00 GMT-0800 (PST) The start of today in the user's timezone
yesterday Dec 31 2014 00:00:00 GMT-0800 (PST) The start of yesterday in the user's timezone
today 3pm error Not supported
2 days before yesterday error Not supported

Auto-generated time columns

Interana automatically creates four fields derived from your event data: __day__, __hour__, __minute__, and __week__. These return the first second of the selected time period (minute, hour, day, week) in which the event occurred. 

Charts display the values in epoch time format, in milliseconds. If you use a Group By comparison, Interana will display the group labels in a timestamp format. 

For example, if an event occurred at 10:48 am on Tuesday, January 5, 2016, the fields will return readable values as chart labels when used in Group by operations: 

  • __day__ will return the first second of that day: 1/5/2016 00:00:00 AM
  • __hour__ will return the first second of the hour in which the event occurred: 1/5/2016 10:00:00 AM
  • __minute__ will return the first second of the minute: 1/5/2016 10:48:00 AM
  • __week__ will return the first second of the first day of that week (in this case, Sunday, January 3): 1/3/2016 00:00:00 AM 

You can use these fields with the Count Unique measure to build cohorts, sessions, metrics, and queries.

Automatic calendar alignment for queries

As of version 2.19, when a query’s resolution is greater than one day, the last day of a 1-week, 2-week, 4-week, 13-week, or 52-week time bucket will now end on the day that contains the end time of the query. For example, If the query end time is January 20, 2016 at 2 pm, the weekly time bucket will be end on January 21, 2016 at 12 am for this query, including all of its subqueries.

If your Interana instance is configured to use a specific day as the first day of the week, Interana will not perform this automatic calendar alignment. The instance configuration setting takes precedence over the automatic alignment. For example, if your instance is configured to use Monday as the beginning of the week, weeks will always be aligned to run from Monday to Monday.

Tags recommended by the template: article:reference

  • Was this article helpful?