Skip to content

Instantly share code, notes, and snippets.

@thepaul
Created January 6, 2024 17:41
Show Gist options
  • Save thepaul/d32f0e2ac483010d9bfc3dec61c505e2 to your computer and use it in GitHub Desktop.
Save thepaul/d32f0e2ac483010d9bfc3dec61c505e2 to your computer and use it in GitHub Desktop.
Decode the timestamp embedded in a ULID into a TIMESTAMPTZ
SELECT
'epoch'::TIMESTAMPTZ
+
(
'x'
||
lpad(
substr(
replace(
the_uuid::STRING,
'-',
''
),
1,
12
),
16,
'0'
)
)::BIT(64)::INT8
*
'1 millisecond'::INTERVAL
+
(
'x'
||
lpad(
substr(
the_uuid::STRING,
15,
3
),
4,
'0'
)
)::BIT(16)::INT2
*
'1 microsecond'::INTERVAL
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment