Description:
Create builtin function to extract the Timestamp portion of the UUID with microsecond precision to MySQL Timestamp datatype. If the datatype passed to the function is a Binary datatype, the function should be able to handle a Low/High swap flag.
This is useful when using a UUID primary key, and accurately populating a 'created' column with the same time as that of the generated UUID.
How to repeat:
For reference, this is the sample code that I used for a generated 'created' column based on a Binary UUID 'id' column with Swapped Low/High timestamp:
`id` BINARY(16) -- UUID modified with high to low timestamp in Binary format
NOT NULL
DEFAULT ( -- If the ID is not explicitly supplied, generate a DEFAULT.....
UUID_TO_BIN(
uuid(), -- Get a current UUID
TRUE -- Swap the low and high order timestamp bits (high at highest order) for efficiency.
) -- Convert the modified UUID to binary for fast efficient indexing
) -- Finally assign the binary modified UUID to the id field
PRIMARY KEY,
`created` TIMESTAMP(6) -- DateTime created Timestamp with microsecond precision.
GENERATED ALWAYS AS ( -- Will extract the Timestamp from the modified binary UUID id field.
FROM_UNIXTIME(
cast(
round(
cast(
conv(
hex(
left(
(id << 4) >> 4 -- shift the ID left 4 bits then right 4 bits to remove any of them that are set, then....
,8) -- take the left 8 bytes which represents the number of 100 nanoseconds since Oct 15, 1582, the base date for the UUID spec, which is the date of the Gregorian reform to the Christian calendar.
), -- Change the 100 nanosecond timestamp to HEX for conversion, then....
16,
10
) -- convert from Hex (base 16) to Decimal (base 10)...
as decimal(19)
) -- and cast it to a 19 digit Decimal....
/10 -- divide by 10 to go to microseconds....
) -- and round off the 100 nano decimal to the nearest microsecond.
as decimal (24,6) -- Setup the result so we can change to whole seconds with microsecond decimal precision....
)
/1000000 -- ...then divide to change from microseconds to full seconds, retaining the microsecond portion to the right of the decimal. Now we have the number of seconds with microsecond precision since October 15, 1582.
- (141427 * 24 * 60 * 60) -- Now we subtract the number of seconds between that date and January 1, 1970 to get a UNIX Timestamp value, retaining microsecond precision.
) -- Lastly, we convert the UNIX Time value to the internal MySQL Timestamp Datatype.
)
NOT NULL,
Description: Create builtin function to extract the Timestamp portion of the UUID with microsecond precision to MySQL Timestamp datatype. If the datatype passed to the function is a Binary datatype, the function should be able to handle a Low/High swap flag. This is useful when using a UUID primary key, and accurately populating a 'created' column with the same time as that of the generated UUID. How to repeat: For reference, this is the sample code that I used for a generated 'created' column based on a Binary UUID 'id' column with Swapped Low/High timestamp: `id` BINARY(16) -- UUID modified with high to low timestamp in Binary format NOT NULL DEFAULT ( -- If the ID is not explicitly supplied, generate a DEFAULT..... UUID_TO_BIN( uuid(), -- Get a current UUID TRUE -- Swap the low and high order timestamp bits (high at highest order) for efficiency. ) -- Convert the modified UUID to binary for fast efficient indexing ) -- Finally assign the binary modified UUID to the id field PRIMARY KEY, `created` TIMESTAMP(6) -- DateTime created Timestamp with microsecond precision. GENERATED ALWAYS AS ( -- Will extract the Timestamp from the modified binary UUID id field. FROM_UNIXTIME( cast( round( cast( conv( hex( left( (id << 4) >> 4 -- shift the ID left 4 bits then right 4 bits to remove any of them that are set, then.... ,8) -- take the left 8 bytes which represents the number of 100 nanoseconds since Oct 15, 1582, the base date for the UUID spec, which is the date of the Gregorian reform to the Christian calendar. ), -- Change the 100 nanosecond timestamp to HEX for conversion, then.... 16, 10 ) -- convert from Hex (base 16) to Decimal (base 10)... as decimal(19) ) -- and cast it to a 19 digit Decimal.... /10 -- divide by 10 to go to microseconds.... ) -- and round off the 100 nano decimal to the nearest microsecond. as decimal (24,6) -- Setup the result so we can change to whole seconds with microsecond decimal precision.... ) /1000000 -- ...then divide to change from microseconds to full seconds, retaining the microsecond portion to the right of the decimal. Now we have the number of seconds with microsecond precision since October 15, 1582. - (141427 * 24 * 60 * 60) -- Now we subtract the number of seconds between that date and January 1, 1970 to get a UNIX Timestamp value, retaining microsecond precision. ) -- Lastly, we convert the UNIX Time value to the internal MySQL Timestamp Datatype. ) NOT NULL,