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?
 
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
CAMPzxzxDeathzxzxAuthor Commented:
You rock!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.