SQL to trim Statserver tables?

[daveg]

I have started using statserver tables, but would like to schedule a SQL script to delete entries more than x days/months old. My problem is in selecting records older than x, because I don't know how to translate epoch time to current or vice versa. Can anyone let me have a script for MSSQL which selects by (epoch time < no of days old)?

[Kevin]

As long as you're willing to accept a margin of error, you can use the dateadd() function to add the epoch time to '01/01/1970 0:00' for unix (See below) When you add, make sure you specify to add by "seconds". This should give you the times of the records in GMT. (your "margin of error" is the time zone difference between GMT and your time zone).

Once you get this time calculated, use the datediff() function to remove any rows that are more than X days from today.

I use a script similar to this to keep my log table pruned to 60 days for Standard and 30 days for others.

Warning: make sure you test the calculation for the date and time. If memory serves, Windows epoch time is '01/01/1980 0:00', but I am not sure if Genesys always uses Unix epoch time or uses epoch time based on the platform.

[daveg]

The following worked for deleting records over 31 days old:

delete from voice_reasons
where (dateadd(ss, start_time, '1/1/1970')) <
(CONVERT (DATETIME,GETDATE()-31,101))

Thanks for the help.