Search code examples
mongodbprestotrino

Get timestamp from MongoDB ObjectId though PrestoDB

I'm using PrestoDB to query some MongoDB collections. MongoDB has a getTimestamp() method to get the timestamp portion of an ObjectId. How can I get a similar timestamp on PrestoDB?

Solution

  • It's not implemented in Presto, but there is a PR: https://github.com/prestosql/presto/pull/3089

    You can implement this with eg

    @ScalarFunction("get_timestamp")
@SqlType(StandardTypes.TIMESTAMP_WITH_TIME_ZONE) // ObjectId's timestamp is a point in time
public static long getTimestamp(@SqlType("ObjectId") Slice value)
{
    int epochSeconds = new ObjectId(value.getBytes()).getTimestamp();
    return DateTimeEncoding.packDateTimeWithZone(TimeUnit.SECONDS.toMillis(epochSeconds), UTC_KEY);
}

    -- add this in the https://github.com/prestosql/presto/blob/master/presto-mongodb/src/main/java/io/prestosql/plugin/mongodb/ObjectIdFunctions.java class