CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

Oracle SQL -- Can I timestamp a query?

I have a simple query:

Select         Count(p.Group_ID)
From           Player_Source P 
  Inner Join   Feature_Group_Xref X On P.Group_Id=X.Group_Id 
where          x.feature_name ='Try this site'

which spits out the current number of people in a specific test group at the current moment in time.

If I wanted to see what this number was, say, on 9/10/12 instead, could I add something in to the query to time phase this information as the database had it 2 days ago?

Upvotes: 1

Views: 125

Answers (2)

Justin Cave
Justin Cave

Reputation: 231791

It depends...

It is at least theoretically possible that you could use flashback query

Select Count(p.Group_ID)
  From Player_Source as of timestamp( date '2012-09-10' ) P 
       Join Feature_Group_Xref as of timestamp( date '2012-09-10' ) X 
         On P.Group_Id=X.Group_Id 
 where x.feature_name ='Try this site'

This requires, though, that you have the privileges necessary to do a flashback query and that there is enough UNDO for Oracle to apply to be able to get back to the state those tables were in at midnight two days ago. It is unlikely that the database is configured to retain that much UNDO though it is generally possible. This query would also work if you happen to be using Oracle Total Recall.

More likely, though, you will need to modify your schema definition so that you are storing historical information that you can then query as of a point in time. There are a variety of ways to accomplish this-- adding effective and expiration date columns to the table as @ruakh suggests is one of the more popular options. Which option(s) are appropriate in your particular case will depend on a variety of factors including how much history you want to retain, how frequently data changes, etc.

Upvotes: 1

ruakh
ruakh

Reputation: 183504

No. If you want to store historical information, you will need to incorporate that into your schema. For example, you might extend Feature_Group_Xref to add the columns Effective_Start_Timestamp and Effective_End_Timestamp; to find which groups currently have a given feature, you would write AND Effective_End_Timestamp > CURRENT_TIMESTAMP() (or AND Effective_End_Timestamp IS NULL, depending how you want to define the column), but to find which groups had a given feature at a specific time, you would write AND ... BETWEEN Effective_Start_Timestamp AND Effective_End_Timestamp (or AND Effective_Start_Timestamp < ... AND (Effective_End_Timestamp > ... OR Effective_End_Timestamp IS NULL)).

Wikipedia has a good article on various schema designs that people use to tackle this sort of problem: see http://en.wikipedia.org/wiki/Slowly_changing_dimension.

Upvotes: 3

Related Questions