Last active
March 28, 2022 12:04
-
-
Save rainbowdashlabs/ffcba7eabade7783116377d40e42a9b2 to your computer and use it in GitHub Desktop.
Convert a discord snowflake to unix timestamp using PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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