Time query syntax reference

When working in the Scuba Explorer and dashboard view, you can select a time range for your query. Scuba 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.

Scuba does not support dates prior to January 1, 1970 (the beginning of Unix epoch time). 

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, Scuba uses midnight, or the zeroth millisecond of the specified calendar date.

Times 

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

Scuba 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, Scuba uses the current date.

Milliseconds since the Unix Epoch 

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

Relative time syntax 

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

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

 A "month" is defined as 30 days. Months are not calendar-aligned.  

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. 

Ago and before 

For time ranges measured in seconds, minutes, or hours: ago and before will reference the same time. For example if the current time is 3:15 PM, a query starting 12 hours ago or 12 hours before will begin at 3:15 AM. 

For time ranges one day or greater:

• Ago will reference the exact time. For example, if the current time is 3:15 PM on July 10, 1 day ago will result in 3:15 PM July 9. 

• Before will snap to the beginning of that day. For example, if the current time is 3:15 PM on July 10, 1 day before will result in 12:00 AM July 9. Similarly, 1 week ago will result in 12:00 AM July 3.

Today, yesterday, and now 

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

Days of the week 

Scuba supports the name of days as keywords (Monday, Tuesday, etc.). The exact dates represented by these terms are relative to the current day. 

• If the day specified in the query is between the start of the week (Sunday) to the day before the current day, inclusive, Scuba sets the date to midnight of the closest previous day.

• If the day specified in the query is the current day to the end of the week (Saturday), inclusive, Scuba sets the date to 1 week before the specified day. 

For example, if the current day is Monday, March 13 2017:

Scuba will return an error if you run a query that violates the implicit time range (for example, starting on "Wednesday" and ending on "Tuesday"). 

Due to possible ambiguity about the exact date that is being referenced, Scuba does not recommend using day of the week keywords.

Auto-generated time columns 

Scuba 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, Scuba 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 Scuba instance is configured to use a specific day as the first day of the week, Scuba 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.