Period (period)
- Juan Tomás Alonso Nieto (Deactivated)
Description
Adjust a series of timestamps to make them coincide with a specific duration period and taking into account the UTC value of the timestamp. This means that timestamps will be adjusted so they correspond to the beginning of the time period to which they belong. These periods will be determined by the duration specified in the arguments.
How does it work in the search window?
Select Create column in the search window toolbar, then select the Period operation.
This operation needs two arguments: The Date to transform into periods and the Duration of them.
Argument | Data type | Description |
|---|---|---|
Date mandatory | timestamp | You can either select a column with that data type or introduce it manually. In case you want to introduce it, note that this value should be a date: Year-Month_Day Hour:Minute:Second.Millisecond (yyyy-MM-dd HH:mm:ss.SSS) → You can skip seconds and milliseconds. |
Duration mandatory | duration / integer | Duration: an integer number plus d for days, h for hours, m for minutes, s for seconds Integer: an integer number to represent milliseconds. |
The data type of the new column values will be timestamp and the values shown will be an adjustment of the original values to make them coincide with the duration period specified in the arguments. This way they correspond to the beginning of the period to which they belong. For example, 27 minutes will be 15 if the duration specified in the arguments is 15 minutes (15m), 20 if the duration is 10 minutes (10m) or 0 is the duration is (30m).
Example
In the demo.ecommerce.data table, we want to adjust the dates in our eventdate column to periods of 15 seconds.
The arguments needed to create the new column are:
Date - eventdate column
Duration - Click the pencil icon and enter 15s
Click Create column and you will see the following result:
14 belongs to the first period of 15 seconds and it is thus shown as 0, the starting point of the first period.
15 belongs to the second period of 15 seconds and it is thus shown as 15, the starting point of the second period.
16 belongs to the second period of 15 seconds and it is thus shown as 15, the starting point of the second period.
Using hours
Be aware that when you use hours, the periods will be calculated based on the UTC but the results will be shown in your time zone. In the following example, the duration established is three hours and the time zone is UTC+1.
The logical periods established with that duration would be then 0, 3, 6, 9, 12, 15, 18 and 21; however, the picture shows 19 and 22. This is because 18 and 21 in UTC are 19 and 22 UTC+1.
How does it work in LINQ?
Use the operator select... as... and add the operation syntax to create the new column. These are the valid formats for the Period operation:
period(timestamp, duration)period(timestamp, integer)
Example
You can copy the following LINQ scripts and try the previous examples on the demo.ecommerce.data table.
from demo.ecommerce.data
select period(eventdate, 15s) as eventdate_periodfrom demo.ecommerce.data
select period(eventdate, 3h) as eventdate_period