1

In MongoDB you can retrieve the date from an ObjectId using the getTimestamp() function. How can I retrieve the date from a MongoDB ObjectId using Postgresql (e.g., in the case where such an ObjectId is stored in a Postgres database)?

Example input:

507c7f79bcf86cd7994f6c0e

Wanted output:

2012-10-15T21:26:17Z

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
user1175817
  • 449
  • 2
  • 7
  • 17

2 Answers2

3

In Mongodb documentation the Objectid is formed with a timestamp as the first 4 bytes, but this is represented in hexidecimal. Assuming that hexidecimal value is stored as a string in PostgreSQL, then the following query will extract just the first 8 characters of that objectid, convert that to an integer (which is seconds from 1970-01-01) then convert that integer to a timestamp. For example:

SELECT TO_TIMESTAMP(int_val) ts_val
FROM (
    SELECT ('x' || lpad(left(objectid,8), 8, '0'))::bit(32)::int AS int_val
    FROM   (
       VALUES ('507c7f79bcf86cd7994f6c0e') 
       ) AS t1(objectid)
    ) AS t2
;

Converting a hexadecimal string to integer is discussed here: Convert hex in text representation to decimal number

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

The first answer is quite excellent. This one expands the answer by making a reusable function out of it.

create function extractMongoTimestamp(text) RETURNS TIMESTAMP WITH TIME ZONE
as
'SELECT TO_TIMESTAMP(int_val) ts_val
FROM (
    SELECT (''x'' || lpad(left(objectid,8), 8, ''0''))::bit(32)::int AS int_val
    FROM   (
       VALUES ($1)
       ) AS t1(objectid)
    ) AS t2'
language sql
immutable
RETURNS null on null input;

Use it in your query:

select extractMongoTimestamp('507c7f79bcf86cd7994f6c0e');
FormBucket Support
  • 1