Table has BigInt timestamp - need to delete rows older than 7 days

I need to write a SP to delete rows that have a timestamp 7 days or older from this table..

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LiveCoinAllOrderBookBids]') AND type in (N'U'))
--DROP TABLE [dbo].[LiveCoinAllOrderBookBids]
--GO
--IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'LiveCoinAllOrderBookBids')
--BEGIN
--CREATE TABLE [dbo].[LiveCoinAllOrderBookBids](
--	[LiveCoinAllOrderBookBidsID] [int] IDENTITY(1,1) NOT NULL,
--	[LiveCoinAllOrderBookID] [int] NOT NULL,
--	[BidQuantity] DECIMAL(18,8) NOT NULL,
--	[Bid] DECIMAL(18,8) NOT NULL,
--	[TimeStamp] BIGINT NOT NULL,			 
--	CONSTRAINT [PK_LiveCoinAllOrderBookBids] PRIMARY KEY CLUSTERED 
--	(
--		[LiveCoinAllOrderBookBidsID] ASC
--	))	
--END
--GO

Open in new window

CAMPzxzxDeathzxzxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Since there's an identity field, let's assume for the moment that rows are entered with a current Timestamp value.  

>I need to write a SP to delete rows that have a timestamp 7 days or older from this table..
Then it's as simple as (try both of these out and see which one is faster)

-- 1
Declare @id int
SELECT @id = MAX(id) FROM LiveCoinAllOrderBookBids WHERE Timestamp < DATEADD(day, -7, GETDATE())
DELETE FROM LiveCoinAllOrderBookBids WHERE id >= @id

-- 2
DELETE FROM LiveCoinAllOrderBookBids
WHERE Timestamp >= DATEADD(day, -7, GETDATE())

Open in new window

0
Scott PletcherSenior DBACommented:
You cannot rely on an identity, because any value can be inserted at any time.

If you lookup in this table often by timestamp, you should cluster the table on timestamp, not on identity.  The biggest and most damaging, to performance, myth in table design is that "every tables should be clustered by an identity column."  FALSE, FALSE, FALSE.
0
CAMPzxzxDeathzxzxAuthor Commented:
Hey Scott - glad to see that you are still around!

I must be missing something here because the TimeStamp is a BigInt ---  [TimeStamp] BIGINT NOT NULL

I think there has to be some kind of convert right?

DELETE FROM LiveCoinAllOrderBookBids
WHERE Timestamp >= DATEADD(day, -7, GETDATE())
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott PletcherSenior DBACommented:
Thanks!

Select a few rows and see what the format is. Presumably it's long like YYYYMMDDHHMMSS.  Once we see what the pattern is, it's easy enough to gen a bigint to match that pattern.
0
CAMPzxzxDeathzxzxAuthor Commented:
It does come from a C# Long type.
example = 1519142686097
0
CAMPzxzxDeathzxzxAuthor Commented:
I just looked at the code and found it is C# Long to BigInt before it is sp'ed to the db into the BigInt column.  I'll post a new question regarding the best way to handle these dates.  I'm modeling against APIs that kick out UNIX UTC.
0
Scott PletcherSenior DBACommented:
My best guess is that it's millisec since Jan 01, 1970 (common in Unix-based systems).  If so, then:

 1519142686097 = 2018-02-20 16:04:46.097

and the code for 7 days back from the current day will be

WHERE TimeStamp >=  CAST(DATEDIFF(SECOND, '19700101', DATEADD(DAY, -7, CAST(GETDATE() AS date))) AS bigint) * 1000
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CAMPzxzxDeathzxzxAuthor Commented:
You rock!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.