Time functions

55 min

Time functions work with timestamps, time intervals, and time ranges in a formula. Use them to filter events to a period, group data by day or month, or measure durations. Relative times, time ranges, and the time_bucket function use the time zone of your Analytics app. The extract_* functions read timestamps as stored, without time zone conversion. Before you start, review the NetScript language reference for formula syntax.

DATE TIME

Use this expression to enter an absolute timestamp, such as the start date of a campaign. Enter the timestamp in the format yyyy-mm-dd hh:mm:ss.SSS, where the milliseconds (SSS) are optional.

Function

DATE TIME

Data type – timestamp

Example

2022-07-15 11:46:35.123

Result

2022-07-15 11:46:35.123

N TIME_UNIT

Use this expression to define a time interval, such as the length of a session window. Replace N with a number and TIME_UNIT with a unit of time. Valid units are millisecond, second, minute, hour, day, month, and year, in singular or plural form. NetScript has no week or quarter unit, so use 7 days or 3 months instead.

Function

N TIME_UNIT

Data type – time interval

Example

1 hour
4 days

Result

A time interval of one hour
A time interval of four days

N TIME_UNIT ago or fromnow

Use this expression to shift the current time backward or forward, such as one hour ago. The expression has the following parts:

N – The number of time units to shift.

TIME_UNIT – The unit of time, such as hour or day.

ago or fromnow – Use ago to shift backward and fromnow to shift forward. The two-word form from now works the same as fromnow.

By default, Analytics rounds the result up to the end of its time unit. For month and year units, Analytics rounds to the end of the day instead. Add exact after the expression to use the exact time, such as 1 hour ago exact.

Function

N TIME_UNIT ago
N TIME_UNIT fromnow

Data type – timestamp

Example

1 hour ago
4 days fromnow
1 hour ago exact

Result

Current timestamp: 2022-07-15 02:58:18.123
2022-07-15 02:00:00
2022-07-20 00:00:00
2022-07-15 01:58:18.123

DIRECTION N TIME_UNIT

Use this expression to define a time range relative to the current time, such as the last 30 days. Use the range to filter data to a period that moves with the current date. The expression has the following parts:

DIRECTION – Enter last for a past range or next for an upcoming range.

N – Enter the number of time units that the range covers.

TIME_UNIT – Enter the unit of time, such as hour or day.

By default, Analytics rounds each end of the range up to the end of its time unit. For example, last 7 days ends at the end of the current day. For month and year units, Analytics rounds to the end of the day instead. A next range without exact starts at the end of the current time unit, so next 7 days excludes today. Add exact after the expression to start or end the range at the current timestamp instead. For example, use last 2 hours exact.

Function

DIRECTION N TIME_UNIT
DIRECTION N TIME_UNIT exact

Data type – time range

Example

last 2 hours exact
Events | count | where (time in last 7 days)

Result

Current timestamp: 2022-07-15 11:46:35.123
2022-07-15 09:46:35.123 to 2022-07-15 11:46:35.123
The number of events from the seven days that end with the current day

TIMEPOINT_1 to TIMEPOINT_2

Use this expression to define a time range between two time points, such as one year ago up to now. Each time point is a timestamp, a relative time, or now.

Function

TIMEPOINT_1 to TIMEPOINT_2

Data type – time range

Example

1 years ago to now
Events | count | where (time in 2022-01-01 00:00:00 to 2022-07-01 00:00:00)

Result

The time range from one year ago to now
The number of events from January 1, 2022, through June 30, 2022

getstart(TIME_RANGE)

Use the getstart function to return the start of a time range as a timestamp. Use it with the $TIME_RANGE parameter to reference the start of the exploration time range. The get_start alias works the same way.

Function

getstart(TIME_RANGE)

Data type – timestamp

Example

getstart($TIME_RANGE)

Result

The start of the exploration time range

getend(TIME_RANGE)

Use the getend function to return the end of a time range as a timestamp. Use it with the $TIME_RANGE parameter to reference the end of the exploration time range. The get_end alias works the same way.

Function

getend(TIME_RANGE)

Data type – timestamp

Example

getend($TIME_RANGE)

Result

The end of the exploration time range

TIMESTAMP_1 - TIMESTAMP_2

Use the - operator to calculate the duration between two timestamps, such as the time between sign-up and first purchase. When the first timestamp is earlier, the result is negative.

Function

TIMESTAMP_1 - TIMESTAMP_2

Data type – time interval

Example

2020-07-21 05:28:47 - 2020-07-16 05:28:47

Result

5 days

TIMEPOINT + TIME_INTERVAL

Use the + operator to shift a time point forward by a time interval, such as one hour later.

Function

TIMEPOINT + TIME_INTERVAL

Data type – timestamp

Example

2024-07-15 1:58:18 + 1 hour

Result

2024-07-15 2:58:18

TIMEPOINT - TIME_INTERVAL

Use the - operator to shift a time point backward by a time interval, such as one hour earlier.

Function

TIMEPOINT - TIME_INTERVAL

Data type – timestamp

Example

2022-07-15 1:58:18 - 1 hour

Result

2022-07-15 00:58:18

TIME_INTERVAL_1 + TIME_INTERVAL_2

Use the + operator to add two time intervals, such as a trial period plus a grace period.

Function

TIME_INTERVAL_1 + TIME_INTERVAL_2

Data type – time interval

Example

1 days + 3 days

Result

4 days

TIME_INTERVAL_1 - TIME_INTERVAL_2

Use the - operator to subtract one time interval from another.

Function

TIME_INTERVAL_1 - TIME_INTERVAL_2

Data type – time interval

Example

14 days - 6 days

Result

8 days

extract_second(TIMESTAMP)

Use the extract_second function to get the number of whole seconds since the start of the minute.

Function

extract_second(TIMESTAMP)

Data type – number

Example

extract_second(2022-07-15 12:58:18.123)

Result

extract_minute(TIMESTAMP)

Use the extract_minute function to get the number of whole minutes since the start of the hour.

Function

extract_minute(TIMESTAMP)

Data type – number

Example

extract_minute(2022-07-15 12:58:18.123)

Result

extract_hour(TIMESTAMP)

Use the extract_hour function to get the number of whole hours since the start of the day.

Function

extract_hour(TIMESTAMP)

Data type – number

Example

extract_hour(2022-07-15 12:58:18.123)

Result

extract_day_of_week(TIMESTAMP)

Use the extract_day_of_week function to get the day of the week as a number. Use it to compare weekday and weekend traffic. The numbering depends on your data warehouse. On BigQuery and Databricks, Sunday is 1 and Saturday is 7. On Snowflake and Redshift, Sunday is 0 and Saturday is 6. On Snowflake, this numbering applies when the WEEK_START parameter has its default value of 0.

Function

extract_day_of_week(TIMESTAMP)

Data type – number

Example

extract_day_of_week(2022-07-15 12:58:18.123)

Result

6 on BigQuery and Databricks, or 5 on Snowflake and Redshift

extract_day_of_month(TIMESTAMP)

Use the extract_day_of_month function to get the day of the month as a number. For example, the first day of the month is 1.

Function

extract_day_of_month(TIMESTAMP)

Data type – number

Example

extract_day_of_month(2022-07-15 12:58:18.123)

Result

extract_day_of_year(TIMESTAMP)

Use the extract_day_of_year function to get the day of the year as a number. For example, January 1 is 1.

Function

extract_day_of_year(TIMESTAMP)

Data type – number

Example

extract_day_of_year(2022-01-11 12:58:18.123)

Result

extract_week(TIMESTAMP)

Use the extract_week function to get the week number of the year. The numbering depends on your data warehouse:

Snowflake, Redshift, and Databricks – Use International Organization for Standardization (ISO) 8601 weeks, which start on Monday. Week 1 contains the first Thursday of the year.

BigQuery – Uses weeks that start on Sunday. Days before the first Sunday of the year are in week 0.

Function

extract_week(TIMESTAMP)

Data type – number

Example

extract_week(2022-01-02 02:30:00)
extract_week(2022-01-03 02:30:00)

Result

52
1

In ISO 8601 numbering, January 2, 2022, falls in the last week of 2021. On BigQuery, both examples return 1.

extract_month(TIMESTAMP)

Use the extract_month function to get the month of the year as a number. For example, January is 1.

Function

extract_month(TIMESTAMP)

Data type – number

Example

extract_month(2022-01-11 12:58:18.123)
extract_month(2022-04-01 12:58:18.123)

Result

1
4

extract_quarter(TIMESTAMP)

Use the extract_quarter function to get the quarter of the year as a number. For example, January 1 through March 31 is quarter 1.

Function

extract_quarter(TIMESTAMP)

Data type – number

Example

extract_quarter(2022-07-15 12:58:18.123)

Result

extract_year(TIMESTAMP)

Use the extract_year function to get the year of a timestamp.

Function

extract_year(TIMESTAMP)

Data type – number

Example

extract_year(2022-07-15 12:58:18.123)

Result

time

Use the time keyword to reference the timestamp of an event dataset without naming its timestamp column. Use it with an event dataset that has a designated timestamp column. The time keyword needs no arguments.

Function

time

Data type – timestamp

Example

Events | count by (time_bucket(time, 1 day))
Orders | count | where (time in last 7 days)

Result

The number of events for each day
The number of orders from the last seven days

time_bucket(TIMESTAMP, N TIME_UNIT, TIME_ZONE)

Use the time_bucket function to group timestamps into fixed-size intervals (buckets), such as one bucket for each day. The function returns the start time of the bucket that contains the timestamp. Buckets of an hour or longer follow the time zone of your Analytics app. Buckets that are a multiple of seven days start on a Monday. Month and year buckets align to calendar months.

The function takes the following arguments:

TIMESTAMP – (Optional) The date and time to evaluate. If you omit it, the function uses the event timestamp, time.

N TIME_UNIT – (Optional) The size of each bucket, such as 1 hour. Valid units are millisecond, second, minute, hour, day, month, and year. If you omit it, the function uses the $TIME_GRAIN parameter.

TIME_ZONE – (Optional) The time zone for the buckets, such as 'America/Los_Angeles'. If you omit it, the function uses the time zone of your Analytics app.

Function

time_bucket()
time_bucket(TIMESTAMP)
time_bucket(TIMESTAMP, N TIME_UNIT)
time_bucket(TIMESTAMP, N TIME_UNIT, TIME_ZONE)

Data type – timestamp

Example

time_bucket(2022-07-15 12:58:18.123, 1 hour)

Result

2022-07-15 12:00:00

Use now to refer to the current time. For example, the following formula returns the start of the current three-month bucket:

time_bucket(now, 3 months)

To get the time three months from now, use 3 months fromnow exact.

Shorthand functions such as hour(TIMESTAMP), day(TIMESTAMP), and month(TIMESTAMP) work like time_bucket with a bucket size of one unit.

convert_timezone(FROM_TIME_ZONE, TO_TIME_ZONE, TIMESTAMP)

Use the convert_timezone function to convert a timestamp from one time zone to another. For example, convert Coordinated Universal Time (UTC) to Pacific Time. Enter time zone names in the tz database format, such as 'America/Los_Angeles'.

Function

convert_timezone(FROM_TIME_ZONE, TO_TIME_ZONE, TIMESTAMP)

Data type – timestamp

Example

convert_timezone('Etc/UTC', 'America/Los_Angeles', 2023-01-01 00:00:00)

Result

2022-12-31 16:00:00

to_millis(TIME_INTERVAL)

Use the to_millis function to convert a time interval to a whole number of milliseconds. The time interval cannot use month or year units.

Function

to_millis(TIME_INTERVAL)

Data type – number

Example

to_millis(2023-01-01 00:00:01 - 2023-01-01 00:00:00)

Result