TIME_SLICE
Aggregates data by different fixed-time intervals and returns a rounded-up input TIMESTAMP
value to a value that corresponds with the start or end of the time slice interval.
Given an input TIMESTAMP
value such as 2000-10-28 00:00:01
, the start time of a 3-second time slice interval is 2000-10-28 00:00:00
, and the end time of the same time slice is 2000-10-28 00:00:03
.
Behavior Type
Syntax
TIME_SLICE( expression, slice-length [, 'time‑unit' [, 'start‑or‑end' ] ] )
Parameters
expression |
One of the following:
Vertica evaluates expression on each row. |
slice-length |
A positive integer that specifies the slice length. |
time‑unit |
Time unit of the slice, one of the following:
|
start‑or‑end |
Specifies whether the returned value corresponds to the start or end time with one of the following strings:
This parameter can be included only if you also supply a non-null time‑unit argument. |
Null Argument Handling
TIME_SLICE
handles null arguments as follows:
TIME_SLICE
returns an error when any one of slice-length, time‑unit, or start‑or‑end parameters is null.- If expression is null and slice-length, time‑unit, or start‑or‑end contain legal values,
TIME_SLICE
returns a NULL value instead of an error.
Usage
The following command returns the (default) start time of a 3-second time slice:
=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3); TIME_SLICE --------------------- 2009-09-19 00:00:00 (1 row)
The following command returns the end time of a 3-second time slice:
=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3, 'SECOND', 'END'); TIME_SLICE --------------------- 2009-09-19 00:00:03 (1 row)
This command returns results in milliseconds, using a 3-second time slice:
=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3, 'ms'); TIME_SLICE ------------------------- 2009-09-19 00:00:00.999 (1 row)
This command returns results in microseconds, using a 9-second time slice:
=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3, 'us'); TIME_SLICE ---------------------------- 2009-09-19 00:00:00.999999 (1 row)
The next example uses a 3-second interval with an input value of '00:00:01'. To focus specifically on seconds, the example omits date, though all values are implied as being part of the timestamp with a given input of '00:00:01'
:
- '00:00:00' is the start of the 3-second time slice
- '00:00:03' is the end of the 3-second time slice.
- '00:00:03' is also the start of the second 3-second time slice. In time slice boundaries, the end value of a time slice does not belong to that time slice; it starts the next one.
When the time slice interval is not a factor of 60 seconds, such as a given slice length of 9 in the following example, the slice does not always start or end on 00 seconds:
=> SELECT TIME_SLICE('2009-02-14 20:13:01', 9);
TIME_SLICE
---------------------
2009-02-14 20:12:54
(1 row)
This is expected behavior, as the following properties are true for all time slices:
- Equal in length
- Consecutive (no gaps between them)
- Non-overlapping
To force the above example ('2009-02-14 20:13:01') to start at '2009-02-14 20:13:00', adjust the output timestamp values so that the remainder of 54 counts up to 60:
=> SELECT TIME_SLICE('2009-02-14 20:13:01', 9 )+'6 seconds'::INTERVAL
AS time; time --------------------- 2009-02-14 20:13:00
(1 row)
Alternatively, you could use a different slice length, which is divisible by 60, such as 5:
=> SELECT TIME_SLICE('2009-02-14 20:13:01', 5); TIME_SLICE --------------------- 2009-02-14 20:13:00 (1 row)
A TIMESTAMPTZ value is implicitly cast to TIMESTAMP. For example, the following two statements have the same effect.
=> SELECT TIME_SLICE('2009-09-23 11:12:01'::timestamptz, 3); TIME_SLICE --------------------- 2009-09-23 11:12:00 (1 row) => SELECT TIME_SLICE('2009-09-23 11:12:01'::timestamptz::timestamp, 3); TIME_SLICE --------------------- 2009-09-23 11:12:00 (1 row)
Examples
You can use the SQL analytic functions FIRST_VALUE
and LAST_VALUE
to find the first/last price within each time slice group (set of rows belonging to the same time slice). This structure can be useful if you want to sample input data by choosing one row from each time slice group.
=> SELECT date_key, transaction_time, sales_dollar_amount,TIME_SLICE(DATE '2000-01-01' + date_key + transaction_time, 3), FIRST_VALUE(sales_dollar_amount) OVER (PARTITION BY TIME_SLICE(DATE '2000-01-01' + date_key + transaction_time, 3) ORDER BY DATE '2000-01-01' + date_key + transaction_time) AS first_value FROM store.store_sales_fact LIMIT 20; date_key | transaction_time | sales_dollar_amount | time_slice | first_value ----------+------------------+---------------------+---------------------+------------- 1 | 00:41:16 | 164 | 2000-01-02 00:41:15 | 164 1 | 00:41:33 | 310 | 2000-01-02 00:41:33 | 310 1 | 15:32:51 | 271 | 2000-01-02 15:32:51 | 271 1 | 15:33:15 | 419 | 2000-01-02 15:33:15 | 419 1 | 15:33:44 | 193 | 2000-01-02 15:33:42 | 193 1 | 16:36:29 | 466 | 2000-01-02 16:36:27 | 466 1 | 16:36:44 | 250 | 2000-01-02 16:36:42 | 250 2 | 03:11:28 | 39 | 2000-01-03 03:11:27 | 39 3 | 03:55:15 | 375 | 2000-01-04 03:55:15 | 375 3 | 11:58:05 | 369 | 2000-01-04 11:58:03 | 369 3 | 11:58:24 | 174 | 2000-01-04 11:58:24 | 174 3 | 11:58:52 | 449 | 2000-01-04 11:58:51 | 449 3 | 19:01:21 | 201 | 2000-01-04 19:01:21 | 201 3 | 22:15:05 | 156 | 2000-01-04 22:15:03 | 156 4 | 13:36:57 | -125 | 2000-01-05 13:36:57 | -125 4 | 13:37:24 | -251 | 2000-01-05 13:37:24 | -251 4 | 13:37:54 | 353 | 2000-01-05 13:37:54 | 353 4 | 13:38:04 | 426 | 2000-01-05 13:38:03 | 426 4 | 13:38:31 | 209 | 2000-01-05 13:38:30 | 209 5 | 10:21:24 | 488 | 2000-01-06 10:21:24 | 488 (20 rows)
TIME_SLICE
rounds the transaction time to the 3-second slice length.
The following example uses the analytic (window) OVER clause to return the last trading price (the last row ordered by TickTime) in each 3-second time slice partition:
=> SELECT DISTINCT TIME_SLICE(TickTime, 3), LAST_VALUE(price)OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
If you omit the windowing clause from an analytic clause, LAST_VALUE
defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. Results can seem non-intuitive, because instead of returning the value from the bottom of the current partition, the function returns the bottom of the window, which continues to change along with the current input row that is being processed. For more information, see Time Series Analytics and SQL Analytics in Analyzing Data.
In the next example, FIRST_VALUE
is evaluated once for each input record and the data is sorted by ascending values. Use SELECT DISTINCT
to remove the duplicates and return only one output record per TIME_SLICE
:
=> SELECT DISTINCT TIME_SLICE(TickTime, 3), FIRST_VALUE(price)OVER (PARTITION BY TIME_SLICE(TickTime, 3) ORDER BY TickTime ASC) FROM tick_store; TIME_SLICE | ?column? ---------------------+---------- 2009-09-21 00:00:06 | 20.00 2009-09-21 00:00:09 | 30.00 2009-09-21 00:00:00 | 10.00 (3 rows)
The information output by the above query can also return MIN
, MAX
, and AVG
of the trading prices within each time slice.
=> SELECT DISTINCT TIME_SLICE(TickTime, 3),FIRST_VALUE(Price) OVER (PARTITION BY TIME_SLICE(TickTime, 3) ORDER BY TickTime ASC), MIN(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)), MAX(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)), AVG(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)) FROM tick_store;