UUID/GUID v7 Generator

[jpbro]

I needed to generate UUID/GUID version 7 values and thought I would share the module in case anyone else here needs to do the same.

A little background on UUIDv7 is available here:

https://en.wikipedia.org/wiki/Univer...mp_and_random)

Key Features of UUIDv7

• Time-Based Sorting: The leading timestamp allows for chronological ordering of UUIDs, which is beneficial for database performance.
  
• Uniqueness: The remaining bits ensure that each UUID is unique, minimizing the risk of duplication.
  
• Database Efficiency: The structure helps reduce index fragmentation, improving write performance in databases.
  

What UUIDv7 values are generally useful for:

• Database primary keys, especially where insertion order matters and random UUIDv4 keys would cause unnecessary index fragmentation.
  
• Distributed record identifiers generated independently by multiple processes or servers, without requiring a central identity service.
  
• Log, audit, event, message, request, job, or session identifiers where chronological sorting is useful during troubleshooting or reporting.
  
• Offline-created records that may later be merged or synchronized with records created elsewhere.
  
• Public record identifiers where exposing an approximate creation time is acceptable—or even desirable—and unpredictability is not itself a security boundary.

Disclosure: The initial pass was generated by ChatGPT, but it was fairly slow, so I made some manual improvements. After that, I did a second back-and-forth pass with ChatGPT to tweeze out the last bit of performance and generate a full test suite to verify that:

1. The basic structure of the generated UUIDs is correct.
   
2. The various formatting options work as expected.
   
3. No collisions are detected.
   
4. The date/time extraction methods work as expected.
   
5. The “self-ordering” nature of UUIDv7 is evident.

Performance
On my machine, the compiled module (with the usual optimizations) generates about 850,000 GUIDs per second.

Public Methods
There are three public methods whose behaviour should be fairly self-evident from their names:

• NewGuidV7
• DateUtcFromGuidV7
• DateLocalFromGuidV7

These methods have no external dependencies beyond a few Windows API calls.

Source Code

Here is the main source code:

' === modGuidV7.bas ===
Option Explicit

' Provides generation and timestamp extraction for UUID/GUID version 7
' identifiers as defined by RFC 9562.
'
' UUIDv7 places a 48-bit Unix timestamp, expressed in milliseconds, at the
' beginning of the identifier. This gives UUIDv7 values useful chronological
' ordering when compared in their normal textual or network-byte order.
'
' UUIDv7 is a good fit for identifiers that benefit from global uniqueness
' and approximate creation-time ordering, for example:
'
'   - Database primary keys, especially where insertion order matters and
'     random UUIDv4 keys would cause unnecessary index fragmentation.
'
'   - Distributed record identifiers generated independently by multiple
'     processes or servers without requiring a central identity service.
'
'   - Log, audit, event, message, request, job, or session identifiers where
'     chronological sorting is useful during troubleshooting or reporting.
'
'   - Offline-created records that may later be merged or synchronized with
'     records created elsewhere.
'
'   - Public record identifiers where exposing an approximate creation time
'     is acceptable (or desired) and unpredictability is not itself a security boundary.
'
' UUIDv7 is usually not appropriate for:
'
'   - Password-reset links, login tokens, API credentials, bearer tokens, or
'     any other value whose secrecy alone grants access.
'
'   - Situations where the creation timestamp must not be inferable from the
'     identifier such as secrets or authorization tokens
'
'   - A strict global sequence or gap-free ordering requirement. UUIDv7 values
'     are time-ordered identifiers, not a replacement for a database sequence.

' This UUIDv7 implementation uses:
'
'   - GetSystemTimeAsFileTime to obtain the current UTC time directly,
'     avoiding local-time and UTC-offset calculations.
'
'   - A process-local 12-bit sequence in the UUID rand_a field to preserve
'     ordering when more than one UUID is generated in the same millisecond,
'     or when the system clock moves backward.
'
'   - RtlGenRandom for the remaining 62 random bits. This avoids the identical
'     startup sequences and limited seeding entropy of ordinary VB6 PRNGs,
'     which is especially important when multiple processes generate UUIDs.
'
'   - SystemTimeToTzSpecificLocalTimeEx when extracting a local timestamp so
'     Windows can apply the time-zone and daylight-saving rules appropriate
'     to the timestamp, rather than merely applying today's UTC offset.
'
' The monotonic sequence is local to this process. UUIDs generated by separate
' processes may use the same timestamp and sequence, but retain 62 independently
' generated random bits for collision resistance.

Public Enum e_GuidV7FormatOptions
   ' Controls the textual representation returned by NewGuidV7.
   '
   ' The options are bit flags and may be combined with Or:
   '
   '   guidv7fmt_None
   '      Uppercase hexadecimal with no dashes or braces:
   '      00112233445566778899AABBCCDDEEFF
   '
   '   guidv7fmt_LowerCase
   '      Uses lowercase hexadecimal:
   '      00112233445566778899aabbccddeeff
   '
   '   guidv7fmt_InnerDashes
   '      Inserts the standard GUID section separators:
   '      00112233-4455-6677-8899-AABBCCDDEEFF
   '
   '   guidv7fmt_OuterBraces
   '      Encloses the result in braces:
   '      {00112233445566778899AABBCCDDEEFF}
   '
   '   guidv7fmt_Default
   '      Standard uppercase GUID format with dashes and no braces:
   '      00112233-4455-6677-8899-AABBCCDDEEFF
   '
   '   guidv7fmt_All
   '      Lowercase, dashed, and enclosed in braces:
   '      {00112233-4455-6677-8899-aabbccddeeff}
   '
   ' Example:
   '   NewGuidV7(guidv7fmt_LowerCase Or guidv7fmt_InnerDashes)
   '   produces:
   '   00112233-4455-6677-8899-aabbccddeeff
   
   guidv7fmt_None = 0
   
   guidv7fmt_LowerCase = &H1
   guidv7fmt_InnerDashes = &H2
   guidv7fmt_OuterBraces = &H4
   
   guidv7fmt_Default = guidv7fmt_InnerDashes
   guidv7fmt_All = guidv7fmt_LowerCase Or guidv7fmt_InnerDashes Or guidv7fmt_OuterBraces
End Enum

Private Type SYSTEMTIME
   wYear         As Integer
   wMonth        As Integer
   wDayOfWeek    As Integer
   wDay          As Integer
   wHour         As Integer
   wMinute       As Integer
   wSecond       As Integer
   wMilliseconds As Integer
End Type

Private Type FILETIME
   dwLowDateTime  As Long
   dwHighDateTime As Long
End Type

Private Declare Function SystemTimeToTzSpecificLocalTimeEx Lib "kernel32" ( _
   ByVal lpTimeZoneInformation As Long, _
   ByRef lpUniversalTime As SYSTEMTIME, _
   ByRef lpLocalTime As SYSTEMTIME _
) As Long

Private Declare Sub GetSystemTimeAsFileTime Lib "kernel32" ( _
   ByRef lpSystemTimeAsFileTime As FILETIME)

Private Declare Function RtlGenRandom Lib "advapi32.dll" _
   Alias "SystemFunction036" ( _
   ByRef RandomBuffer As Any, _
   ByVal RandomBufferLength As Long _
) As Long

Private Declare Sub CopyMemory Lib "kernel32" _
   Alias "RtlMoveMemory" ( _
   ByRef Destination As Any, _
   ByRef Source As Any, _
   ByVal Length As Long _
)

' The UUIDv7 timestamp field is an unsigned 48-bit integer.
Private Const MAX_UUIDV7_TIMESTAMP_MS As Double = 281474976710655#

' Lookup table vars for Hex conversions
Private m_HexByteStringsU(0 To 255) As String
Private m_HexByteStringsL(0 To 255) As String
Private m_HexByteStringsInitialized As Boolean

Public Function NewGuidV7(Optional ByVal p_FormatOptions As e_GuidV7FormatOptions = guidv7fmt_Default) As String
   
   Static s_LastMs As Double
   Static s_Seq As Long

   Dim la_Guid(0 To 15) As Byte
   Dim l_Milliseconds As Double
   Dim l_Value As Double
   Dim ii As Long

   ' UUIDv7 timestamps have whole-millisecond precision. Truncating here also
   ' prevents CByte from rounding a remainder such as 255.9 up to 256.
   l_Milliseconds = Fix(CurrentUnixMilliseconds())
   
   If l_Milliseconds < 0# Or _
      l_Milliseconds > MAX_UUIDV7_TIMESTAMP_MS Then
      
      Err.Raise 5, _
                "NewGuidV7", _
                "The UTC date is outside the UUIDv7 timestamp range."
   End If

   ' Keep identifiers ordered within this process even if the operating-system
   ' clock is adjusted backward.
   If l_Milliseconds < s_LastMs Then
      l_Milliseconds = s_LastMs
   End If

   If l_Milliseconds = s_LastMs Then
      s_Seq = s_Seq + 1

      ' rand_a contains 12 bits, allowing 4096 ordered identifiers at one
      ' timestamp. If exhausted, move the logical timestamp forward rather
      ' than blocking until the physical clock advances.
      If s_Seq > &HFFF& Then
         l_Milliseconds = l_Milliseconds + 1#
         s_Seq = 0
      End If
   Else
      s_Seq = 0
   End If

   s_LastMs = l_Milliseconds

   ' UUID fields are encoded in network byte order. Repeated division extracts
   ' the least-significant byte while filling the timestamp array backward.
   l_Value = l_Milliseconds

   For ii = 5 To 0 Step -1
      la_Guid(ii) = CByte(l_Value - Fix(l_Value / 256#) * 256#)

      l_Value = Fix(l_Value / 256#)
   Next

   ' Only bytes 8 through 15 require random data. Bytes 6 and 7 are occupied
   ' by the version and monotonic sequence. Two high bits of byte 8 are later
   ' replaced by the RFC variant, leaving 62 random bits.
   If RtlGenRandom(la_Guid(8), 8&) = 0 Then
      Err.Raise vbObjectError, _
                "NewGuidV7", _
                "RtlGenRandom was unable to generate random data."
   End If

   ' Byte 6 contains the version in its high nibble and the high four bits
   ' of the 12-bit rand_a sequence in its low nibble.
   la_Guid(6) = CByte(&H70 Or ((s_Seq \ &H100&) And &HF&))
   la_Guid(7) = CByte(s_Seq And &HFF&)

   ' RFC 9562 variant: the two most-significant bits must be binary 10.
   la_Guid(8) = CByte(&H80 Or (la_Guid(8) And &H3F))

   NewGuidV7 = ByteArrayToGuidStrLookup(la_Guid, p_FormatOptions)
End Function

Public Function DateUtcFromGuidV7(ByVal p_GuidV7 As String) As Date
   Const UNIX_EPOCH_AS_VB_DATE As Double = 25569#
   Const MILLISECONDS_PER_DAY As Double = 86400000#
   
   Dim l_Guid As String
   Dim l_Milliseconds As Double
   Dim l_Byte As Long
   Dim ii As Long
   
   ' Normalize common GUID representations before examining fixed UUID fields.
   l_Guid = Trim$(p_GuidV7)
   
   ' Remove optional enclosing braces before normalizing the inner format.
   If Left$(l_Guid, 1) = "{" Or Right$(l_Guid, 1) = "}" Then
      If Left$(l_Guid, 1) <> "{" Or Right$(l_Guid, 1) <> "}" Then
         Err.Raise 5, _
                   "DateUtcFromGuidV7", _
                   "Invalid GUID brace format."
      End If
      
      l_Guid = Mid$(l_Guid, 2, Len(l_Guid) - 2)
   End If
   
   l_Guid = Replace$(l_Guid, "-", vbNullString)
   
   If Len(l_Guid) <> 32 Then
      Err.Raise 5, _
                "DateUtcFromGuidV7", _
                "A GUID must contain exactly 32 hexadecimal digits."
   End If

   ' The version nibble must be checked before interpreting the first 48 bits
   ' as a UUIDv7 Unix timestamp.
   If Mid$(l_Guid, 13, 1) <> "7" Then
      Err.Raise 5, "DateUtcFromGuidV7", _
                "The supplied GUID is not a version 7 GUID."
   End If
   
   ' UUIDs conforming to the RFC variant have a first variant digit from
   ' 8 through B, corresponding to the required binary prefix 10.
   Select Case UCase$(Mid$(l_Guid, 17, 1))
   Case "8", "9", "A", "B"
      ' Valid RFC 9562 variant.
      
   Case Else
      Err.Raise 5, "DateUtcFromGuidV7", _
                "The supplied GUID does not have the RFC 9562 variant."
   End Select
   
   ' Accumulating with value = value * 256 + byte avoids requiring a native
   ' unsigned 48-bit integer type. VB6 Double represents every 48-bit integer
   ' exactly because it has 53 bits of integer precision.
   Dim l_Hex As String
   
   l_Hex = "&H--"
   For ii = 1 To 11 Step 2
      Mid$(l_Hex, 3) = Mid$(l_Guid, ii, 2)
      l_Byte = CByte(l_Hex)
      l_Milliseconds = l_Milliseconds * 256# + l_Byte
   Next
   
   DateUtcFromGuidV7 = CDate(UNIX_EPOCH_AS_VB_DATE + l_Milliseconds / MILLISECONDS_PER_DAY)
End Function

Public Function DateLocalFromGuidV7(ByVal p_GuidV7 As String) As Date
   ' Convert the extracted UTC timestamp using the historical time-zone rules
   ' applicable to that date, rather than applying the computer's current
   ' UTC offset.
   
   DateLocalFromGuidV7 = DateUtcToLocal(DateUtcFromGuidV7(p_GuidV7))
End Function

Private Function DateUtcToLocal(ByVal p_DateUtc As Date) As Date
   Dim l_UtcSystemTime As SYSTEMTIME
   Dim l_LocalSystemTime As SYSTEMTIME
   
   With l_UtcSystemTime
      .wYear = Year(p_DateUtc)
      .wMonth = Month(p_DateUtc)
      .wDay = Day(p_DateUtc)
      .wHour = Hour(p_DateUtc)
      .wMinute = Minute(p_DateUtc)
      .wSecond = Second(p_DateUtc)
      .wMilliseconds = MillisecondFromDate(p_DateUtc)
   End With
   
   ' A null time-zone pointer tells Windows to use the computer's configured
   ' dynamic time zone, including historical daylight-saving transitions
   ' where those rules are available.
   If SystemTimeToTzSpecificLocalTimeEx( _
         0&, _
         l_UtcSystemTime, _
         l_LocalSystemTime) = 0 Then
      
      Err.Raise vbObjectError + 1000, _
                "DateUtcToLocal", _
                "Unable to convert the UTC date to local time."
   End If
   
   With l_LocalSystemTime
      DateUtcToLocal = _
         DateSerial(.wYear, .wMonth, .wDay) + _
         TimeSerial(.wHour, .wMinute, .wSecond) + _
         CDbl(.wMilliseconds) / 86400000#
   End With
End Function

Private Function MillisecondFromDate(ByVal p_Date As Date) As Long
   Dim l_TotalMilliseconds As Double
   
   ' VB Date is floating-point and may represent an exact millisecond a tiny
   ' fraction below its intended value. The small bias prevents truncation to
   ' the preceding millisecond without being large enough to affect a genuine
   ' adjacent millisecond.
   l_TotalMilliseconds = Fix(CDbl(p_Date) * 86400000# + 0.0001)
   
   MillisecondFromDate = CLng( _
      l_TotalMilliseconds - _
      Fix(l_TotalMilliseconds / 1000#) * 1000#)
End Function

Private Function CurrentUnixMilliseconds() As Double
   Const UNIX_EPOCH_FILETIME_MS As Double = 11644473600000#

   Dim l_FileTime As FILETIME
   Dim l_MillisecondsSince1601 As Currency

   GetSystemTimeAsFileTime l_FileTime

   ' FILETIME is a 64-bit count of 100-nanosecond intervals since 1601.
   ' Currency is also stored as a 64-bit integer, with an implied scale of
   ' 10,000. Because one millisecond contains exactly 10,000 FILETIME units,
   ' copying the bits into Currency makes its displayed numeric value equal
   ' to milliseconds since 1601 without requiring 64-bit integer arithmetic.
   
   CopyMemory l_MillisecondsSince1601, l_FileTime, 8&

   CurrentUnixMilliseconds = Fix(CDbl(l_MillisecondsSince1601) - UNIX_EPOCH_FILETIME_MS)
End Function

Private Sub InitializeHexByteStrings()
   Dim ii As Long

   If m_HexByteStringsInitialized Then Exit Sub

   ' Precomputing all byte representations removes sixteen Hex$/Right$
   ' conversions from every generated GUID. This was measurably faster than
   ' both per-byte conversion and direct UTF-16 buffer construction in VB6.
   For ii = 0 To 255
      m_HexByteStringsU(ii) = Right$("0" & Hex$(ii), 2)
      m_HexByteStringsL(ii) = LCase$(m_HexByteStringsU(ii))
   Next

   m_HexByteStringsInitialized = True
End Sub

Private Function ByteArrayToGuidStrLookup(ByRef pa_Guid() As Byte, _
                                          ByVal p_FormatOptions As e_GuidV7FormatOptions) As String
   Dim l_Result As String
   Dim l_StartOffset As Long
   Dim l_SepOffset As Long
   Dim l_SepStep As Long
   Dim l_HasBraces As Boolean
   Dim l_HasDashes As Boolean
   Dim l_ResultLength As Long
      
   InitializeHexByteStrings

   ' Beginning with hyphens already in place permits fixed-position Mid$
   ' assignments and avoids branching or position calculations in this
   ' performance-sensitive path.
   l_HasBraces = (p_FormatOptions And guidv7fmt_OuterBraces) <> 0
   
   l_HasDashes = (p_FormatOptions And guidv7fmt_InnerDashes) <> 0
   
   ' A compact GUID has 32 hexadecimal characters, plus four optional
   ' internal dashes and two optional enclosing braces.
   l_ResultLength = 32
   If l_HasDashes Then l_ResultLength = l_ResultLength + 4
   If l_HasBraces Then l_ResultLength = l_ResultLength + 2
   
   l_Result = String$(l_ResultLength, "-")
   
   If l_HasBraces Then
      Mid$(l_Result, 1, 1) = "{"
      Mid$(l_Result, l_ResultLength, 1) = "}"
      l_StartOffset = 1
   End If
   
   If Not l_HasDashes Then
      l_SepOffset = 1
      l_SepStep = 1
   End If
   
   If (p_FormatOptions And guidv7fmt_LowerCase) <> 0 Then
      Mid$(l_Result, 1 + l_StartOffset, 2) = m_HexByteStringsL(pa_Guid(0))
      Mid$(l_Result, 3 + l_StartOffset, 2) = m_HexByteStringsL(pa_Guid(1))
      Mid$(l_Result, 5 + l_StartOffset, 2) = m_HexByteStringsL(pa_Guid(2))
      Mid$(l_Result, 7 + l_StartOffset, 2) = m_HexByteStringsL(pa_Guid(3))
   
      Mid$(l_Result, 10 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(4))
      Mid$(l_Result, 12 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(5))
      l_SepOffset = l_SepOffset + l_SepStep
      
      Mid$(l_Result, 15 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(6))
      Mid$(l_Result, 17 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(7))
      l_SepOffset = l_SepOffset + l_SepStep
   
      Mid$(l_Result, 20 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(8))
      Mid$(l_Result, 22 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(9))
      l_SepOffset = l_SepOffset + l_SepStep
   
      Mid$(l_Result, 25 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(10))
      Mid$(l_Result, 27 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(11))
      Mid$(l_Result, 29 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(12))
      Mid$(l_Result, 31 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(13))
      Mid$(l_Result, 33 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(14))
      Mid$(l_Result, 35 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsL(pa_Guid(15))
   
   Else
      Mid$(l_Result, 1 + l_StartOffset, 2) = m_HexByteStringsU(pa_Guid(0))
      Mid$(l_Result, 3 + l_StartOffset, 2) = m_HexByteStringsU(pa_Guid(1))
      Mid$(l_Result, 5 + l_StartOffset, 2) = m_HexByteStringsU(pa_Guid(2))
      Mid$(l_Result, 7 + l_StartOffset, 2) = m_HexByteStringsU(pa_Guid(3))
   
      Mid$(l_Result, 10 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(4))
      Mid$(l_Result, 12 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(5))
      l_SepOffset = l_SepOffset + l_SepStep
      
      Mid$(l_Result, 15 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(6))
      Mid$(l_Result, 17 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(7))
      l_SepOffset = l_SepOffset + l_SepStep
   
      Mid$(l_Result, 20 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(8))
      Mid$(l_Result, 22 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(9))
      l_SepOffset = l_SepOffset + l_SepStep
   
      Mid$(l_Result, 25 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(10))
      Mid$(l_Result, 27 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(11))
      Mid$(l_Result, 29 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(12))
      Mid$(l_Result, 31 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(13))
      Mid$(l_Result, 33 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(14))
      Mid$(l_Result, 35 + l_StartOffset - l_SepOffset, 2) = m_HexByteStringsU(pa_Guid(15))
   End If
   
   ByteArrayToGuidStrLookup = l_Result
End Function

Source Code and Test Project

The full test suite does have an RC6 dependency for high-resolution timing and collision detection using cCollection.Exists.

The complete project, including the test suite, is available here:

GUIDV7.zip


Enjoy!