Find rows older than 60 days with Epoch time format

Question

Find rows older than 60 days with Epoch time format

Isabelle2378

SSCertifiable

Points: 6174
More actions
December 4, 2019 at 5:12 pm

#3704409
Hi,
I'm trying to purge this table for any rows older than 60 days but the LogTime column is in Epoch format. I've read articles on how to convert it to human readable format and i just can't get it to work. Here is the table data.
LogLevel LogTime
INFO 1574434804509
INFO 1574434804509
INFO 1574434804509
Here's the sql to purge rows old than 60 days but it comes up with zero rows. What am I doing wrong?
```
delete 
from dbo.MecLog
WHERE DATEADD(SECOND, (LogTime/1000), '19700101') < dateadd(dd, -60,(getdate()))
```
Thanks!
Bea Isabelle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100940 More actions · Answer 1

Don't see anything directly wrong the calcs, although you should not do a calc on every LogTime column in every row, you should compare LogTime directly to the 60-days-ago EPOCH value, like so:

DELETE
FROM dbo.MecLog
WHERE LogTime < (SELECT CAST(DATEDIFF(SECOND, '19700101', DATEADD(DAY, -60, CAST(GETDATE() AS date))) AS bigint) * 1000)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 2

I guess this you need to do this cleaning from time to time, I put my cleaning statements in an own procedure(support.Cleanup), and then make a job to run. May be you need to? When deleting in a live table, you may want to limit the numbers of rows to delete.

Here is a sample, that I use:

------------------------------------------------------------------------
-- Ensure schema
if not exists (select 1 from sys.schemas where name = N'support')
begin
    execute (N'create schema support');
    execute sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Schema for support staff.', @level0type = N'SCHEMA', @level0name = N'support';
end;
go

------------------------------------------------------------------------
-- Name: support.Cleanup
-- Summary:
--   * Clean up the oldest items of data
-- Param:
--     * The number of days to keep.
--     * The max number of items to delete.
--     * The log level to delete.
------------------------------------------------------------------------
create procedure support.Cleanup @daysToKeep int = 60 , @maxItemsToDelete int = 1000, @logLevel varchar(5) = null
as
begin
    set nocount on;
    declare @cleanToTime int = datediff(second, '1970-01-01', dateadd(day, -@daysToKeep, getutcdate()));

    if (@logLevel is null)
    begin
        delete top (@maxItemsToDelete)
        from dbo.MecLog
        where LogTime < @cleanToTime;
    end;
    else
    begin
        set @logLevel = upper(@logLevel);
        delete top (@maxItemsToDelete)
        from dbo.MecLog
        where LogTime < @cleanToTime
            and LogLevel = @logLevel;
    end;
end;
go

If order is important, i.e delete from bottom, add

order by LogTime desc

.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 3

Thank you for your reply! That is very helpful 🙂 I need to run this purge job once a week but unfortunately I don't know how many rows it will generate in this time. My original script was returning zero rows for 60 days but after looking at the other response, it also was returning zero rows. Both scripts work but it only returned rows older than 10 days so i guess it didn't have rows older than 60 days. It is so difficult to tell because the format is not standard time. I will play with the cleanup code that you provided and maybe set it up to run weekly. What I need to do is figure out how many rows there are per month so I can get a better idea of the amount that will be deleted. More testing to do 🙂

Thank you guys for your replies!

Thanks!
Bea Isabelle

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

Isabelle2378 wrote:

Thank you for your reply! That is very helpful 🙂 I need to run this purge job once a week but unfortunately I don't know how many rows it will generate in this time. My original script was returning zero rows for 60 days but after looking at the other response, it also was returning zero rows. Both scripts work but it only returned rows older than 10 days so i guess it didn't have rows older than 60 days. It is so difficult to tell because the format is not standard time. I will play with the cleanup code that you provided and maybe set it up to run weekly. What I need to do is figure out how many rows there are per month so I can get a better idea of the amount that will be deleted. More testing to do 🙂
Thank you guys for your replies!

It would make your life a bit easier if you added a persisted computed column to do the conversion to normal date/time for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)