Extract date from UUID in MYSQL-CodePudding

I am trying to decode uuid in mysql to identify what are the digits contributing to prepare the date part . For e.g. uuid ==> b54adc00-67f9-11d9-9669-0800200c9a66

What is the date part in the above uuid.

CodePudding user response：

I think maybe you are looking for something like this:

select  uuid                           AS uid
,       from_unixtime(
          (conv(                      
            concat(                   
              substring(uuid, 16, 3)
            , substring(uuid, 10, 4)
            , substring(uuid, 1, 8)
            ), 16, 10)                
            div 10 div 1000 div 1000  
          ) - (141427 * 24 * 60 * 60) 
        )                             AS uuid_to_timestamp
,       current_timestamp()           AS timestamp
from  test          ;

https://www.db-fiddle.com/f/4uegQFAyd1NyuSme7F2JwX/1

Reference: https://rpbouman.blogspot.com/2014/06/mysql-extracting-timstamp-and-mac.html?m=0

CodePudding user response：

This only works for variant 1 type uuids. For the obsolete variant 0 or the rarely used variant 2, it would need modification, and other variants do not include dates; the variant is stored in the uuid (unless it is a fully random one) but I am not checking it.

Assuming uuid is the uuid in string form:

select '1582-10-15'
      interval floor(
        conv(
            concat(
                substr(uuid,16,3),
                substr(uuid,10,4),
                substr(uuid,1,8)
            ),
            16,
            10
        )
        /1e7
    ) second

This produces a datetime in UTC; if you need it in your local timezone, convert it like:

convert_tz(
    '1582-10-15'   interval floor(conv(concat(substr(uuid,16,3),substr(uuid,10,4),substr(uuid,1,8)),16,10)/1e7) second,
    ' 00:00',
    'America/Los_Angeles'
)

If you want just the date, wrap one of the above in DATE( ... ).