fboulnois / pg_uuidv7

A tiny Postgres extension to create version 7 UUIDs
Mozilla Public License 2.0
298 stars 25 forks source link

"Boundary" UUID function to create UUID from timestamp #11

Closed msqr closed 1 year ago

msqr commented 1 year ago

I wondered if you would consider adding a function to create "boundary" UUIDs from a given timestamp, to aid in date range query filters. By "boundary" I mean a v7 UUID with the first 48 bits set to a timestamp, but all other non-UUID spec bits set to 0.

For example, a function uuid_v7_boundary(timestamtz) could be used like:

-- "id" column is a v7 UUID
SELECT *
FROM t
WHERE id >= uuid_v7_boundary('2023-09-01Z'::timestamptz)
AND id < uuid_v7_boundary('2023-09-02Z'::timestamptz)

I have been using a SQL function to do this, but I thought it could be a nice feature of this extension.

fboulnois commented 1 year ago

Coincidentally I was working on this earlier, so expect this shortly.