Stack Overflow
Stack Overflow

Questions tagged [bi-temporal]

Bitemporal design denotes both the valid time and transaction time of the data in two timelines with timestamps for both valid times in a temporal database.

In simple words, bitemporal is a way to maintain historical/timeline data and timestamps of the change in data. For example, you can create and save the historical/timeline record of a row every time it changes with a active/inactive flag or using a modified/touched timestamp to the old and newly modified row; either you save every change in the same table or create another(log/archive) table to save the historical changes(and the present table will have only the latest updated row).

32 questions
5
votes
2 answers

Is using only 3 timestamps for a bitemporal SQL database possible?

When implementing a bitemporal database in SQL, it is usually recommended to use the following timestamps: ValidStart ValidEnd TransactionStart TransactionEnd I have used this approach a few times before, but I have always wondered why having…
danielhc
  • 479
  • 1
  • 5
  • 12
5
votes
2 answers

In what cases are bitemporal tables actually used?

I am trying to collect information about temporal databases. I know it is not a modern technology, but I saw that many people who work with databases don't ever know how temporal approach works (I asked some senior programmers and system analysts…
Andrea Gottardi
  • 357
  • 2
  • 3
  • 21
4
votes
1 answer

What's different about Database as a Value in Datomic vs BiTemporal Indexes in Cassandra?

The Datomic database has the concept of 'Database as a Value'. This means that you can get a reference to the database at a point in time - and query against it. It appears that BiTemporal Indexes in Cassandra do almost the same thing. My question…
hawkeye
  • 34,745
  • 30
  • 150
  • 304
4
votes
1 answer

Deleting/Updating BiTemporal Triples in MarkLogic 8

With the introduction of new BiTemporal features in MarkLogic8, you can track changes in two time axes: valid and system times. These features are also supported for triples. So you can go back in time along those two axes and possibly see the…
user5552435
  • 51
  • 5
4
votes
1 answer

Best practice for SCD date pairs (closing / opening timestamps)

When implementing temporal datetime pairs, which is the better approach? Approach 1 _RK FROM_DTTM TO_DTTM 1 01-JAN-2012 00:00:00 31-DEC-2012 23:59:59 ------------> Old record closed out 1 01-JAN-2013 00:00:00 31-DEC-4949…
Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
2
votes
1 answer

Snowflake Query for Latest Snapshot From Bitemporal Data

Given a table of data with bitemporal modeling where there are 2 dates: (i) the date that the data applies to, and (ii) the datetime at which the fact is known City Temp Date As_of_Datetime ——— ———- ———- -——————- Boston 32 …
Ramón J Romero y Vigil
  • 17,373
  • 7
  • 77
  • 125
2
votes
1 answer

How to avoid breaking queries in Temporal Tables after SCHEMA changes?

Imagine I have a temporal table for personal information: UUID (varchar) main_document (varchar) name (varchar) DoB (timestamp) genre (varchar) address (varchar) salary (decimal) at T1 I run a schema migration and add a new column, from now on the…
jfbaro
  • 301
  • 2
  • 10
2
votes
1 answer

Calculating Moving Average on bi-temporal dataset

I'm trying to calculate moving average on a bitemporal dataset. The dataset consists of a data date and an effective date(the date at which the data became available). The data for this date could be restated several times in future(same data date…
Satish
  • 3,020
  • 7
  • 35
  • 47
2
votes
1 answer

Why is the end date in a temporal table the maximum system time and not just NULL

We are looking at implementing a bitemporal solution for a few of our tables, because they must record both application time and system time. I know SQL 2016 has native support for only the system-time component, so we will be converting tables to…
Crogacht
  • 90
  • 1
  • 6
2
votes
0 answers

Bitemporal integrity checks

The need for this comes from the fact, that we now have numerous sources updating sensitive bi-temporal tables, we are a little bit alert and want to cover our backs. I used "Developing Time-Oriented Database Applications in SQL" by Richard…
Palcente
  • 625
  • 2
  • 7
  • 21
1
vote
1 answer

Unable to parse a xml field value from a variable as document version using document-version-uri function in MarkLogic

I am trying the below xquery to add the field value from the xml field - SystemRef which is set in the variable root. I wanted to see the version of the document as - EDI_22000043.xml_2022-11-29T13:59:00.739688Z But I am getting the result as this -…
Rashmita Purkayastha
  • 457
  • 1
  • 8
1
vote
1 answer

Cannot set the document version uri while using the Uni-Temporal document insert in MarkLogic

I am trying to change the document version uri of a unitemporal document while inserting the document in a unitemporal collection. I am running the below xquery , but it throws me the below error. XQuery : xquery version "1.0-ml"; import module…
Rashmita Purkayastha
  • 457
  • 1
  • 8
1
vote
1 answer

Cannot create a valid axes in MarkLogic for temporal document upload

I am trying to implement the bitemporal feature of MarkLogic in a MarkLogic database. For that what I understood is we need to upload the documents in a temporal collection. And we have to first create valid and system axes to define the…
Rashmita Purkayastha
  • 457
  • 1
  • 8
1
vote
1 answer

Bi-temporal SQL table querying

I'm trying to model a SQL table to store salary changes for employees. One approach is using a bi-temporal table as follows: Here an employee was hired on 10/1/2015 with salary of 100,000. Then on 2/15/2016 he had a performance review and his boss…
Alec Bryte
  • 580
  • 1
  • 6
  • 18
1
vote
2 answers

Application-time period tables

I am implementing a bitemporal solution for a few of our tables, using the native temporal table features, and some custom columns and code to handle the application/valid time. However, I just stumbled across a reference to something which is…
Crogacht
  • 90
  • 1
  • 6
1
2 3