Example setup
Load the data.
>>> load_example_data("teradataml", "Employee_roles") # load valid time data.
>>> load_example_data("teradataml", "Employee_Address") # load transaction time data.
>>> load_example_data("teradataml", "Employee") # load bitemporal data.
>>> df1 = DataFrame("Employee_roles")
EmployeeName Department Salary role_validity_period
EmployeeID
1 John Doe IT 100.0 ('20/01/01', '24/12/31')
2 Jane Smith DA 200.0 ('20/01/01', '99/12/31')
3 Bob Marketing 330.0 ('25/01/01', '99/12/31')
3 Bob Sales 300.0 ('24/01/01', '24/12/31')
Example 1: Get the employee roles from DataFrame df1 which are valid at current time
>>> df1.as_of(valid_time="current")
EmployeeName Department Salary EmployeeID 2 Jane Smith DA 200.0 3 Bob Marketing 330.0
Example 2: Get the employee roles from DataFrame df1 which are valid at current time
This example includes a valid time dimension column.
>>> df1.as_of(valid_time="current", include_valid_time_column=True)
EmployeeName Department Salary role_validity_period
EmployeeID
2 Jane Smith DA 200.0 ('20/01/01', '99/12/31')
3 Bob Marketing 330.0 ('25/01/01', '99/12/31')
Example 3: Get the employee roles from DataFrame df1 which are valid at 31st Dec 2026
This example includes a valid time dimension column.
>>> df1.as_of(valid_time="2026-12-31", include_valid_time_column=True)
EmployeeName Department Salary role_validity_period
EmployeeID
2 Jane Smith DA 200.0 ('20/01/01', '99/12/31')
3 Bob Marketing 330.0 ('25/01/01', '99/12/31')
Example 4: Get the employee roles from DataFrame df1 which are valid at 31st Dec 2026
This example includes a valid time dimension column and uses date object instead of string to specify the date.
>>> from datetime import date >>> d = date(2026, 12, 31)
>>> df1.as_of(valid_time=d, include_valid_time_column=True)
EmployeeName Department Salary role_validity_period
EmployeeID
2 Jane Smith DA 200.0 ('20/01/01', '99/12/31')
3 Bob Marketing 330.0 ('25/01/01', '99/12/31')
Example 5: Get the employee roles which are valid between 20th Jan 2018 and 5th March 2024
This example includes a valid time dimension column.
>>> df1.as_of(valid_time=("2018-01-20", "2024-03-05"), include_valid_time_column=True)
EmployeeName Department Salary VALIDTIME
EmployeeID
2 Jane Smith DA 200.0 ('20/01/01', '24/03/05')
1 John Doe IT 100.0 ('20/01/01', '24/03/05')
3 Bob Sales 300.0 ('24/01/01', '24/03/05')
Example 6: Get the employee roles which are valid between 20th Jan 2018 and 5th March 2024
This example does not include valid time dimension column since selecting valid time dimension column is ignored when "valid_time" is a tuple.
>>> df1.as_of(valid_time=(date(2018, 1, 20), "2024-03-05")).as_of(valid_time=date(2023, 1, 1))
EmployeeName Department Salary EmployeeID 2 Jane Smith DA 200.0 1 John Doe IT 100.0
Example 7: Get the employee roles which are valid between 1st Jan 0001 and 1st Jun 2024
>>> df1.as_of(valid_time=(None, date(2024, 3, 5)))
EmployeeName Department Salary VALIDTIME
EmployeeID
2 Jane Smith DA 200.0 ('20/01/01', '24/03/05')
1 John Doe IT 100.0 ('20/01/01', '24/03/05')
3 Bob Sales 300.0 ('24/01/01', '24/03/05')
Example 8: Get the employee roles which are valid between 1st Jun 2024 and 31st Dec 9999
>>> df1.as_of(valid_time=("2024-06-01", None))
EmployeeName Department Salary VALIDTIME
EmployeeID
1 John Doe IT 100.0 ('24/06/01', '24/12/31')
2 Jane Smith DA 200.0 ('24/06/01', '99/12/31')
3 Bob Marketing 330.0 ('25/01/01', '99/12/31')
3 Bob Sales 300.0 ('24/06/01', '24/12/31')
Example 9: Consider df1 as regular DataFrame and retrieve all the records irrespective of whether records are valid or not
>>> df1.as_of(valid_time=None)
EmployeeName Department Salary EmployeeID 1 John Doe IT 100.0 2 Jane Smith DA 200.0 3 Bob Marketing 330.0 3 Bob Sales 300.0
Example 10: Consider df1 as regular DataFrame and retrieve all the records irrespective of whether records are valid or not
This example includes additional period and valid time dimension column.
>>> df1.as_of(valid_time=None, additional_period=("2024-01-01", "2024-03-05"), include_valid_time_column=True)
EmployeeName Department Salary role_validity_period VALIDTIME
EmployeeID
1 John Doe IT 100.0 ('20/01/01', '24/12/31') ('24/01/01', '24/03/05')
2 Jane Smith DA 200.0 ('20/01/01', '99/12/31') ('24/01/01', '24/03/05')
3 Bob Marketing 330.0 ('25/01/01', '99/12/31') ('24/01/01', '24/03/05')
3 Bob Sales 300.0 ('24/01/01', '24/12/31') ('24/01/01', '24/03/05')
>>> df2 = DataFrame("Employee_Address")
EmployeeName address validity_period
EmployeeID
2 Jane Smith 456 Elm St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
1 John Doe 123 Main St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob Johnson 789 Oak St ('2025-03-04 15:41:44.610001+00:00', '9999-12-31 23:59:59.999999+00:00')
Example 11: Consider df2 as regular DataFrame and retrieve all the records including historic records
This example includes a transaction time dimension column.
>>> df2.as_of(transaction_time=None, include_transaction_time_column=True)
EmployeeName address validity_period
EmployeeID
1 John Doe 123 Main St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
2 Jane Smith 456 Elm St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob Johnson 789 Oak Street ('2025-03-04 15:41:44.610000+00:00', '2025-03-04 15:41:44.610001+00:00')
3 Bob Johnson 789 Oak St ('2025-03-04 15:41:44.610001+00:00', '9999-12-31 23:59:59.999999+00:00')
Example 12: Get the employee address which are valid at current time from DataFrame df2
This example includes a transaction time dimension column.
>>> df2.as_of(transaction_time="current", include_transaction_time_column=True)
EmployeeName address validity_period
EmployeeID
2 Jane Smith 456 Elm St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
1 John Doe 123 Main St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob Johnson 789 Oak St ('2025-03-04 15:41:44.610001+00:00', '9999-12-31 23:59:59.999999+00:00')
Example 13: Get the employee address which are valid at current time from DataFrame df2
This example does not include a transaction time dimension column.
>>> df2.as_of(transaction_time="current", include_transaction_time_column=False)
EmployeeName address EmployeeID 2 Jane Smith 456 Elm St 1 John Doe 123 Main St 3 Bob Johnson 789 Oak St
Example 14: Get the employee address which are valid at 2025-03-04 15:41:44.610000+00:00 from DataFrame df2
This example includes a transaction time dimension column.
>>> df2.as_of(transaction_time="2025-03-04 15:41:44.610000+00:00", include_transaction_time_column=True)
EmployeeName address validity_period
EmployeeID
2 Jane Smith 456 Elm St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
1 John Doe 123 Main St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob Johnson 789 Oak Street ('2025-03-04 15:41:44.610000+00:00', '2025-03-04 15:41:44.610001+00:00')
Example 15: Get the employee address which are valid at 2025-03-04 15:41:44.610001+00:00 from DataFrame df2
This example includes a transaction time dimension column.
>>> from datetime import datetime, timezone, timedelta >>> dt = datetime(2025, 3, 4, 15, 41, 44, 610001) >>> dt_with_tz = dt.replace(tzinfo=timezone(timedelta(hours=0))) >>> df2.as_of(transaction_time=dt_with_tz, include_transaction_time_column=True)
EmployeeName address validity_period
EmployeeID
2 Jane Smith 456 Elm St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
1 John Doe 123 Main St ('2025-03-04 15:41:44.610000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob Johnson 789 Oak St ('2025-03-04 15:41:44.610001+00:00', '9999-12-31 23:59:59.999999+00:00')
>>> df3 = DataFrame("Employee")
EmployeeName address Department Salary role_validity validity_period
EmployeeID
1 John Doe 123 Main St IT 100.0 ('20/01/01', '24/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
2 Jane Smith 456 Elm St DA 200.0 ('20/01/01', '99/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob 789 OAK St Marketing 330.0 ('25/01/01', '99/12/31') ('2025-05-06 11:39:25.580000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob 789 Oak St Sales 300.0 ('24/01/01', '24/12/31') ('2025-03-04 18:09:08.830000+00:00', '9999-12-31 23:59:59.999999+00:00')
Example 16: Get all the records from DataFrame df3 by considering the DataFrame as # regular DataFrame
This example includes both valid time and transaction time dimension columns.
>>> df3.as_of(valid_time=None, ... transaction_time=None, ... include_valid_time_column=True, ... include_transaction_time_column=True ... )
EmployeeName address Department Salary role_validity validity_period
EmployeeID
3 Bob 789 Oak Street Sales 300.0 ('24/01/01', '24/12/31') ('2025-03-04 18:08:58.720000+00:00', '2025-03-04 18:09:08.830000+00:00')
3 Bob 789 Oak St Marketing 330.0 ('25/01/01', '99/12/31') ('2025-03-04 18:09:08.830000+00:00', '2025-05-06 11:39:25.580000+00:00')
1 John Doe 123 Main St IT 100.0 ('20/01/01', '24/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
2 Jane Smith 456 Elm St DA 200.0 ('20/01/01', '99/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob 789 Oak Street Marketing 330.0 ('25/01/01', '99/12/31') ('2025-03-04 18:08:58.720000+00:00', '2025-03-04 18:09:08.830000+00:00')
3 Bob 789 OAK St Marketing 330.0 ('25/01/01', '99/12/31') ('2025-05-06 11:39:25.580000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob 789 Oak St Sales 300.0 ('24/01/01', '24/12/31') ('2025-03-04 18:09:08.830000+00:00', '9999-12-31 23:59:59.999999+00:00')
Example 17: Get the employee address from DataFrame df3 which are valid at 1st Jun 2024 from valid time dimension and valid at '2025-03-04 18:09:08.720001+00:00' from transaction time dimension
This example includes both valid time and transaction time dimension columns.
>>> df3.as_of(valid_time="2024-06-01", ... transaction_time="2025-03-04 18:09:08.720001+00:00", ... include_valid_time_column=True, ... include_transaction_time_column=True ... )
EmployeeName address Department Salary role_validity validity_period
EmployeeID
2 Jane Smith 456 Elm St DA 200.0 ('20/01/01', '99/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
1 John Doe 123 Main St IT 100.0 ('20/01/01', '24/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob 789 Oak Street Sales 300.0 ('24/01/01', '24/12/31') ('2025-03-04 18:08:58.720000+00:00', '2025-03-04 18:09:08.830000+00:00')
Example 18: Get the employee address from DataFrame df3 which are valid at 25th Jan 2024 from valid time dimension and valid at current time from transaction time dimension
This example includes a transaction time dimension column.
>>> df3.as_of(valid_time=date(2024, 1, 25), ... transaction_time="current", ... include_transaction_time_column=True)
EmployeeName address Department Salary validity_period
EmployeeID
2 Jane Smith 456 Elm St DA 200.0 ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
1 John Doe 123 Main St IT 100.0 ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00')
3 Bob 789 Oak St Sales 300.0 ('2025-03-04 18:09:08.830000+00:00', '9999-12-31 23:59:59.999999+00:00')
Example 19: Get the employee address from DataFrame df3 which are valid between 1st Jan 2025 and 30th June 2025 from valid time dimension and valid at '2025-03-04 18:08:59.720000+00:00' from transaction time dimension
This example includes both valid time and transaction time dimension columns.
>>> from datetime import datetime, timezone
>>>df3.as_of(valid_time=("2025-01-01", date(2025, 6, 30)),
... transaction_time=datetime(2025, 3, 4, 18, 8, 59, 720000).astimezone(timezone.utc),
... include_transaction_time_column=True)
EmployeeName address Department Salary validity_period VALIDTIME
EmployeeID
2 Jane Smith 456 Elm St DA 200.0 ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00') ('25/01/01', '25/06/30')
3 Bob 789 Oak St Marketing 330.0 ('2025-03-04 18:09:08.830000+00:00', '2025-05-06 11:39:25.580000+00:00') ('25/01/01', '25/06/30')
Example 20: Get the employee address from DataFrame df3 by considering the DataFrame as regular DataFrame from valid time dimension and valid at current time from transaction time dimension
This example adds additional period and include both valid time and transaction time dimension columns.
>>> df3.as_of(valid_time=None,
... transaction_time="current",
... additional_period=("2024-01-01", "2024-03-05"),
... include_valid_time_column=True,
... include_transaction_time_column=True
... )
EmployeeName address Department Salary role_validity validity_period VALIDTIME
EmployeeID
1 John Doe 123 Main St IT 100.0 ('20/01/01', '24/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00') ('24/01/01', '24/03/05')
2 Jane Smith 456 Elm St DA 200.0 ('20/01/01', '99/12/31') ('2025-03-04 18:08:58.720000+00:00', '9999-12-31 23:59:59.999999+00:00') ('24/01/01', '24/03/05')
3 Bob 789 OAK St Marketing 330.0 ('25/01/01', '99/12/31') ('2025-05-06 11:39:25.580000+00:00', '9999-12-31 23:59:59.999999+00:00') ('24/01/01', '24/03/05')
3 Bob 789 Oak St Sales 300.0 ('24/01/01', '24/12/31') ('2025-03-04 18:09:08.830000+00:00', '9999-12-31 23:59:59.999999+00:00') ('24/01/01', '24/03/05')