CODEWITHSUNDEEP
javamongodb
BullyWiiPlaza
BullyWiiPlaza

Reputation: 19185

Formatting MongoDB Timestamp

I have timestamps in a MongoDB database and I want to pretty print them in a readable date format. Using Java and the MongoClient I have the following code:

import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;

import java.sql.Timestamp;

public class MongoDBTest
{
    public static void main(String[] arguments)
    {
        MongoClient client = new MongoClient("127.0.0.1", 27017);
        String databaseName = "my_database";
        MongoDatabase database = client.getDatabase(databaseName);
        String collectionName = "my_collection";
        MongoCollection collection = database.getCollection(collectionName);

        try (MongoCursor<Document> cursor = collection.find().iterator())
        {
            while (cursor.hasNext())
            {
                String json = cursor.next().toJson();
                Document document = Document.parse(json);
                String stringTimestamp = document.get("timestamp").toString();
                Timestamp timestamp = new Timestamp(Long.parseLong(stringTimestamp));
                System.out.println("Timestamp: " + stringTimestamp + " Formatted: " + timestamp);
            }
        }
    }
}

The printed timestamps do not seem to be correct because they are all from 1970 but shouldn't be:

Timestamp: 1357466440 Formatted: 1970-01-16 18:04:26.44
Timestamp: 1357466449 Formatted: 1970-01-16 18:04:26.449
Timestamp: 1357466457 Formatted: 1970-01-16 18:04:26.457
Timestamp: 1357466462 Formatted: 1970-01-16 18:04:26.462
Timestamp: 1357466469 Formatted: 1970-01-16 18:04:26.469
Timestamp: 1357466469 Formatted: 1970-01-16 18:04:26.469
Timestamp: 1357466477 Formatted: 1970-01-16 18:04:26.477
Timestamp: 1357466477 Formatted: 1970-01-16 18:04:26.477

How do I get the "real" formatted dates?

Upvotes: 0

Views: 10581

Answers (2)

Keith
Keith

Reputation: 3151

Per MongoDB documentation:

BSON Date is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This results in a representable date range of about 290 million years into the past and future.

Use the shell's getTimestamp() function to return an ISODate, e.g., ISODate("2012-10-15T21:26:17Z"):

ObjectId.prototype.getTimestamp = function() {
    return new Date(parseInt(this.toString().slice(0,8), 16)*1000);
}

Or use its Java API's ObjectId.getDate(). If you want to use this for queries, here is an example snippet:

// create a date of yesterday
DateTime yesterday = new DateTime().minusDays(1);
Long timestamp = yesterday.getMillis() / 1000L;
String oidString = Long.toHexString(l) + "0000000000000000";

// now find anything newer than that date
ObjectId id = new ObjectId(oidString);
DBObject result = new BasicDBObject("_id", new BasicDBObject("$gt", id));

Upvotes: 1

s7vr
s7vr

Reputation: 75914

Looks like you have timestamp values in seconds. Multiply by 1000 to get into milliseconds.

 String stringTimestamp = document.get("timestamp").toString();
 Timestamp timestamp = new Timestamp(Long.parseLong(stringTimestamp) * 1000);
 Instant instant = timestamp.toInstant();

From here you can use DateTimeFormatter to format. Depending on your need you can also change into LocalDateTime

Upvotes: 1

Related Questions