Skip to content

Instantly share code, notes, and snippets.

@rainbowdashlabs
Last active March 28, 2022 12:04
Show Gist options
  • Save rainbowdashlabs/ffcba7eabade7783116377d40e42a9b2 to your computer and use it in GitHub Desktop.
Save rainbowdashlabs/ffcba7eabade7783116377d40e42a9b2 to your computer and use it in GitHub Desktop.
Convert a discord snowflake to unix timestamp using PostgreSQL
CREATE FUNCTION snowflake_to_unix_timestamp(snowflake BIGINT) RETURNS TIMESTAMP
LANGUAGE plpgsql
PARALLEL SAFE
IMMUTABLE
AS
$BODY$
BEGIN
-- message_id::BIT(64) AS bits
-- bits::BIT(42) AS timestamp_bits
-- timestamp_bits::BIGINT AS discord_epoch
-- discord_epoch + 1420070400000 AS unix
-- to_timestamp(unix / 1000.0) as timestamp
RETURN to_timestamp(( snowflake::BIT(64)::BIT(42)::BIGINT + 1420070400000 ) / 1000.0);
END;
$BODY$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment