CODEWITHSUNDEEP
sqlmongodb
Sicco
Sicco

Reputation: 6271

How to retrieve the date from a MongoDB ObjectId using SQL

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 SQL (e.g., in the case where such an ObjectId is stored in a MySQL database)?

Example input:

507c7f79bcf86cd7994f6c0e

Wanted output:

2012-10-15T21:26:17Z

Upvotes: 6

Views: 3983

Answers (6)

user1464922
user1464922

Reputation: 381

Trino implementation:

select from_unixtime(cast(from_base(substring('507c7f79bcf86cd7994f6c0e', 1, 8), 16) as bigint))

Upvotes: 1

D.Dimitrioglo
D.Dimitrioglo

Reputation: 3663

To the answers above, I'd like to provide an example for BigQuery

SELECT TIMESTAMP_MILLIS(
  CAST(CONCAT('0x', LEFT('6238e198653c381798d93493', 8)) AS INT64) * 1000
)

Upvotes: 0

Amiram Pick
Amiram Pick

Reputation: 21

Redshift

select timestamp 'epoch' + cast (STRTOL(left(_id,8),16) as bigint) * interval '1 second' as my_timestamp

Upvotes: 1

Sicco
Sicco

Reputation: 6271

This can be achieved as follows (assuming objectId is a string) in MySQL:

SELECT FROM_UNIXTIME(
    CAST(CONV(SUBSTR(objectId, 1, 8), 16, 10) AS UNSIGNED)
) FROM table

It works as follows:

  • SUBSTR(objectId, 1, 8) takes the first 8 characters from the hexadecimal objectId string
  • CONV(..., 16, 10) converts the hexadecimal number into a decimal number and returns it as a string (which represents the UNIX timestamp)
  • CAST (...) AS UNSIGNED converts the timestamp string to an unsigned integer
  • FROM_UNIXTIME(...) converts the timestamp integer into the date

Note that by default the displayed date will be based on your system's timezone settings.

Upvotes: 6

CervEd
CervEd

Reputation: 4253

MSSQL

Building on the answer by Teemu

To make some easier to reuse, you can wrap it into your own scalar function like so

CREATE FUNCTION dbo.mongoCreated(@_id CHAR(24))
    RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(
        SECOND,
        CAST(
            CONVERT(
                BINARY(4), '0x' + SUBSTRING(@_id, 1, 8), 1
            ) AS BIGINT
        ),
        CAST('1970-01-01 00:00' AS DATETIME)
    )
END

Postgres

Adapted from another answer to another question

CREATE FUNCTION mongo_timestamp(_id char(24))
    RETURNS TIMESTAMP
    LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN TO_TIMESTAMP(('x' || lpad(LEFT(_id, 8), 8, '0'))::BIT(32)::INT);
END ;
$$
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Upvotes: 0

Teemu Lähteenmäki
Teemu Lähteenmäki

Reputation: 31

For those using SQL Server, similar results would be generated with:

SELECT DATEADD(
    SECOND,
    CAST(
        CONVERT(
            BINARY(4), '0x'+SUBSTRING(@MongoObjectId, 1, 8), 1
        ) AS BIGINT
    ),
    CAST('1970-01-01 00:00' AS DATETIME)
)

Upvotes: 3

Related Questions