File date from Extended Event .xel files

Question

File date from Extended Event .xel files

Michael L John

One Orange Chip

Points: 27069
More actions
January 22, 2025 at 5:21 pm

#4521007

I know this, but I do not remember it. I have scoured by saved scripts as well a lot of google-fu.
I have multiple extended event files.
Activity_0_133820397244150000.xel
Activity_0_133820364382140000.xel
Activity_0_133820332071900000.xel
This portion of the file name is a representation of the date and time, correct???
133820397244150000
133820364382140000
133820332071900000
How can I calculate this????
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

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

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

Jeff Moden SSC Guru Points: 1004495 More actions · Answer 1

This should do it for you... as is typical, the documentation is a lot longer than the code.

 CREATE FUNCTION dbo.Convert_EE_FileName_TimeStamp_to_DATETIME2
/*********************************************************************************************
 Purpose:
 Convert the timestamp embedded into Extended Events .xel files to a DATETIME2 timestamp.
----------------------------------------------------------------------------------------------
 Usage Example:
--===== Variable Example
DECLARE @EE_FileName VARCHAR(540) = 'Activity_0_133820397244151234.xel'
;
 SELECT UTC_DT
   FROM dbo.Convert_EE_FileName_TimeStamp_to_DATETIME2(@EE_FileName)
;
--===== Column Example
 SELECT  st.SomeColumns
        ,eefn.UTC_DT
   FROM dbo.SomeTable st
  CROSS APPLY dbo.Convert_EE_FileName_TimeStamp_to_DATETIME2(st.EE_FileName) eefn
----------------------------------------------------------------------------------------------
 Revision History:
 Rev 00 - 23 Jan 2025 - Jeff Moden
        - Initial creation and unit test.
        - Credit to JYOTHI ALUMALLA for knowing or deriving the correct Epoch Date.
          https://dba.stackexchange.com/a/267436/10312
        - Original problem request...
          https://www.sqlservercentral.com/forums/topic/file-date-from-extended-event-xel-files
*********************************************************************************************/--===== I/O for the function
        (@EE_FileName VARCHAR(540))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== Do the isolation of the timestamp and the conversion to DATETIME2
     -- The FROM clause does the timestamp isolation and the high performanc Integer math is
     -- used to find and add the separate parts to nn Epoch Date of 01 January 1601.
     -- This is necessary because the single part (100 ns increments) are to large to git the
     -- Integer of a DATEADD and there is no DATEADD_BIG.
 SELECT UTC_DT = DATEADD(dd,v.EE_TS/10000000/86400,
                     DATEADD(ss,v.EE_TS/10000000%86400,
                         DATEADD(ns,v.EE_TS%10000000*100,CONVERT(DATETIME2,'1601'))))
    FROM (VALUES (CONVERT(BIGINT,RIGHT(LEFT(@EE_FileName,LEN(@EE_FileName)-4),18))))v(EE_TS)
;
GO

--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)

Michael L John One Orange Chip Points: 27069 More actions · Answer 2

Worked perfectly!

Thank you!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004495 More actions · Answer 3

You bet and good talking with you this morning!

--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)