Description

The TIME_WINDOW() function defines time "slices" or "windows" of the requested size and returns a value identifying which window the specified timestamp argument falls in. This can help you group events or records by time. The size of the window is defined by the first two arguments: the unit of measurement (second, minute, hour, and so on) and the length, which must be an integer. The function returns a TIMESTAMP value identifying the beginning or end of the window, depending on the optional fourth argument. (The default is START, the beginning of the window.)

The valid time unit keywords are:

The TIME_WINDOW function is particularly useful when aggregating data into views based on time. For example, the following view definition uses the TIME_WINDOW() function to count the number of logins grouped into half hour (30 minute) segments:

CREATE VIEW active_users (users, window)
   AS SELECT count(*), TIME_WINDOW(MINUTE, 30, login)
      FROM user_login
      GROUP BY TIME_WINDOW(MINUTE, 30, login);

The time windows themselves are all relative to the standard UNIX start time, midnight January 1, 1970, with the exception of weeks. Weeks are assumed to begin on Monday so any time windows measured in weeks are relative to midnight Monday, December 29, 1969.

Example

The following example uses the TIME_WINDOW() function to group temperature measurements into 2 hour windows, recording the minimum, maximum, and average temperature for each window and location.

CREATE VIEW temp_view (location, window, minimum, maximum, average)
  AS SELECT location, TIME_WINDOW(HOUR, 2, measure_timestamp),
            MIN(temperature), MAX(temperature), AVG(temperature)
     FROM temp_record 
     GROUP BY location, TIME_WINDOW(HOUR, 2, measure_timestamp)