How to subtract from LSN

Question

I need to subtract 5 seconds from a lsn(binary date). What I achieved so far is

select DATEADD(SECOND,-5,sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn()))

But this seems to be more crowded. What I looking for is something intuitive

sys.fn_cdc_get_max_lsn()-0.5`sys.fn_cdc_get_max_lsn()

LSNs are not "binary dates" (and the differences between them don't translate to absolute durations). CDC offers a way to *map* a LSN to a time stamp, but there need not be any LSN that corresponds to "five seconds ago" -- or more accurately, the LSN of the database corresponding to the state it had at that time doesn't uniquely indicate a point in time. This is why you have to map using the function, and can't just mix and match LSNs and times at will. — Jeroen Mostert, Feb 21 '19 at 08:27
What i am trying to achieve is get all the net changes that is recorded 5 second before the last update — Beingnin, Feb 21 '19 at 08:31
All date types *are* binary types. An LNS has no relation to date or time though, which is why `fn_cdc_map_lsn_to_time` is required. An LSN is just a `binary(10)` — Panagiotis Kanavos, Feb 21 '19 at 08:31
@NithinChandran: then you should probably make *that* your question, because there's no (direct) way to map times to LSNs, which is what you need for that. Adding time to the mapped time you get from the LSN gets you only half way. — Jeroen Mostert, Feb 21 '19 at 08:34
@NithinChandran what are you trying to do? Instead of using `fn_cdc_get_max_lsn` you may want to query the [cdc.lsn_time_mapping](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-lsn-time-mapping-transact-sql?view=sql-server-2017) table directly and extract all the LSNs that correspond to the last 5 seconds period — Panagiotis Kanavos, Feb 21 '19 at 08:34

score 4 · Accepted Answer · answered Feb 21 '19 at 08:26

Create a custom function to wrap this up:

CREATE FUNCTION dbo.MyCustomLSNDate ()
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(
            SECOND,
            -5,
            sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn()))
END

You can parametrize the amount of seconds you want to subtract if you need. Once created you can simply write:

SELECT dbo.MyCustomLSNDate()

How to subtract from LSN

1 Answers1