Find rows older than 60 days with Epoch time format

  • Isabelle2378

    SSCertifiable

    Points: 6165

    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

  • ScottPletcher

    SSC Guru

    Points: 98398

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1208

    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: 6165

    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: 995976

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

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

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