In the data explorer can you get the date/time of the last data update to use in the query?

Question

I was building a query that ranked users by the number of votes they cast per day, and it dawned on my that doing the following has a serious flaw (which may well be my fault?):

/* with added line break to avoid horizontal scroll */

ROUND(CAST((U.UpVotes + U.DownVotes) AS FLOAT) 
/ DateDiff(day,CreationDate,getdate()),3) AS VotePerDay

The DateDiff is counting the days from the users creation to the date of the query, but the vote counts are from the users creation to whenever the data explorer was last updated.

So, is there are way to get the date/time of the last data update with a query?
Preferably this done in a manner that can just be dropped in to replace getdate().
And I do not wish to get the user to enter a parameter value.

Answer 1

As a workaround, you could get the date of the most recent vote, which is likely to be very close to the date of the last update, at least on the more popular sites.

select top 1 CreationDate from Votes order by CreationDate desc​

Answer 2

The other answers offer the preferred / logical / canonical solution.

There is a downside to those. Obtaining the MAX(creationdate) (or ORDER BY creationdate) for the tables with many rows now (think Stack Overflow) can take a while. None of the date fields in the SEDE database schema have an index.

Because SEDE is a bit special and we have access to implementation details we can (ab)use that knowledge to get the highest possible date without having the Query Engine perform a full Table Scan.

SQL Server keeps Meta data in its sys schema for the different database objects. One of these meta data tables is sys.tables which inherits the field create_date from its parent, sys.objects.

Each Sunday the sp_Refresh_Database.sql script is run for each database and its main function is to create a new database and recreate and populate all the tables. Once a new database is created, the tables are processeds by the script for example PostsWithDeleted, is created and populated with this statement:

Select * Into PostsWithDeleted 
From [SomeExportServer].[StackOverflow].[dbo].[vExportPostsWithDeleted] 
with (nolock)

This means that the new table PostsWithDeleted gets created right before the last data could enter the database on SomeExportServer. Therefor there will not be any records in PostsWithDeleted after the create_date of the PostWithDeleted table.

With this knowledge you can replace your GetDate() with'

(select create_date from sys.tables where object_id =object_id('PostsWithDeleted'))

Do notice that you get the best results if you use the specific table you're using the dates from as that is the most accurate. On Stack Overflow there is a five hour gap between the first table and last table created. There are some data anomalies due to this effect. For example you can have suggestededits with a postid that is not present yet in the PostsWithDeleted table. The PostId does exist on the site.

If you want to have the latest date where all tables are most likely to be all in sync take the create_date of the Users table because that is always the first table that gets created anew each Sunday.

select create_date from sys.tables where object_id = object_id('Users')

Answer 3

I've created the query latest-date-with-posts to find the last date with data.

It can be used in other queries

DECLARE @EndDateExcluded DateTime

Set @EndDateExcluded = (
    SELECT top 1 cast(Posts.CreationDate as date) AS Date
    FROM Posts ORDER BY Posts.CreationDate des
);

See example in https://data.stackexchange.com/programmers/query/119275/reputation-vs-new-reputation-only-for-last-month