REMAP_TIMESTAMPS
Description
The REMAP_TIMESTAMPS function counts the number of passed time units for given dates since the epoch year (1970-01-01 00:00:00.000). The timestamps for which to calculate the passed time and also the time unit to use, are given as a parameter to the function call. Additionally, the user can specify a CALENDAR configuration which allows to restrict the dates considered in the calculations. For example, using the WEEKDAY_CALENDAR allows to only consider certain valid weekdays in the calculations.
Syntax
REMAP_TIMESTAMPS ( table.column, time_unit [, calendar_specification [, calendar_id_column ] ] )
column: The column containing the timestamps to be remapped.
time_unit: The time unit to map the calculation to. One of
DAYS
,HOURS
,MINUTES
,SECONDS
orMILLISECONDS
calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT.
calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specifications. For more details, please take a look at the respective documentation of the DateTime Calendar.
NULL handling
If the input value is NULL, then the result is NULL as well.
Tips
The query may have more than one calendar specification by using INTERSECT on two or more calendar specifications.
How It Works
In the basic case, the function determines the time between 1970-01-01 00:00:00.000 to the date value in the input timestamp column and returns it as an INT value in the given unit.
Calendar Specification
A calendar specification allows to restrict the accounted DATEs in the calculations to only valid dates according to the calendar's definition. There are currently three supported types of calendars ( WEEKDAY_CALENDAR, FACTORY_CALENDAR and WORKDAY_CALENDAR ) and one way to combine these calendars ( INTERSECT ).
Use Cases
REMAP_TIMESTAMPS
can be used for Throughput Times.
Examples
[1] Calculate the number of days since 1970-01-01 for a constant timestamp. No calendar is specified. | ||||||
| ||||||
|
[2] Calculate the number of days since 1970-01-01 for a constant timestamp (1969-12-01) by using a WEEKDAY_CALENDAR. | ||||||
| ||||||
|
[3] Calculate the number of seconds since 1970-01-01 00:00:00.000 for a timestamp column by using a FACTORY_CALENDAR. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[4] Here we use a WORKDAY_CALENDAR based on a TFACS table for the years 1907, 1974, 2018 and 2019. We want to calculate the number of passed workdays since 1970-01-01 until the specified dates (here null and 1974-2-1). The first date is 'null' which always maps to null. The second input is the first of February in the year 1974. From 1970-01-01 until this date, there are 22 valid workdays. This is because all the years between 1970 and 1974 are not covered by the TFACS table (and thus assumed to be invalid workdays). Only the days between 1974-01-01 and 1974-02-01 are used for the calculation. Within this date range, there are 22 valid workdays in Bavaria Germany. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[5] The following example shows how the time unit influences the result. The first of January 1970 is a Thursday. The 7th of January is a Wednesday. Due to the defined shifts REMAP_TIMESTAMPS takes the following times into account: 1970-01-01 - 4 hours 1970-01-02 - 0 hours 1970-01-03 - 0 hours 1970-01-04 - 0 hours 1970-01-05 - 8 hours 1970-01-06 - 8 hours 1970-01-07 - 7.5 hours That results to 27 hours and 30 minutes. Depending on the timestamps the result is rounded down. DAYS -> 1 HOURS -> 27 MINUTES -> 1650 | ||||||||||||
| ||||||||||||
|
[6] REMAP_TIMESTAMPS with additional Calendar Id column so that different calendar can be used for different activities. | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|