Skip to content

Instantly share code, notes, and snippets.

@thepaul
Created January 6, 2024 17:42
Show Gist options
  • Save thepaul/7e2bd29a6d5b3bffaab386f4292a4382 to your computer and use it in GitHub Desktop.
Save thepaul/7e2bd29a6d5b3bffaab386f4292a4382 to your computer and use it in GitHub Desktop.
Generate a ULID with a specific TIMESTAMPTZ embedded in it
SELECT
overlay(
overlay(
gen_random_ulid()::STRING
PLACING
overlay(
'0' -- caution: prepending '0' is only appropriate until June of 2527
||
to_hex(
(
extract(epoch FROM the_timestamp)
*
1000
)::INT8
)
PLACING
'-'
FROM
9
FOR
0
)
FROM
1
FOR
13
)
PLACING
lpad(
to_hex(
(
extract(epoch FROM the_timestamp)
*
1000000
%
1000
)::INT8
),
3,
'0'
)
FROM
15
FOR
3
)::UUID
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment