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

Immutable

Syntax

TIME_SLICE( expression, slice-length [, 'time‑unit' [, 'start‑or‑end' ] ] )

Parameters

expression

One of the following:

  • Column of type TIMESTAMP
  • String constant that can be parsed into a TIMESTAMP value. For example:

    '2004-10-19 10:23:54'

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:

  • HOUR
  • MINUTE
  • SECOND (default)
  • MILLISECOND
  • MICROSECOND
start‑or‑end

Specifies whether the returned value corresponds to the start or end time with one of the following strings:

  • START (default)
  • END

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.

time_slice boundary rules

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

time_slice factor of 60

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;