Postgresql function to convert a tweet id to the time it was created in milliseconds:
CREATE OR REPLACE FUNCTION tweet_id_to_timestamp(b1 bigint) RETURNS timestamptz AS $$
BEGIN
RETURN to_timestamp(((b1 >> 22) + 1288834974657)::float / 1000);
END; $$
LANGUAGE PLPGSQL;
Conversation
Replying to
Does this function only work for IDs after a certain time? A similar effort of mine would break for anything older than 2015 maybe?
1
1
Should work for any tweet after 2010-06-01 and also any user created after 2013-01-22
2
4
Good point -- I forgot this will work on user ids, too.