Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Reports
    • Case Studies
    • Whitepapers
    • Blog
    • Testimonials
    • Community
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Career
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • My License
  • Download
search

Regularly finding the past week’s new documents when I don’t have a timestamp? #Studio3T_AMA

Posted on: 06/10/2021 by DJ Walker-Morgan

Q: How do I export the last seven days’ new documents from my collections? My documents don’t have a timestamp field but I want to archive them separately.

Welcome to the latest Studio 3T Ask Manatees Anything – #Studio3T_AMA. Want to know how to use Studio 3T to make your MongoDB life better? Just tweet and use #Studio3T_AMA or email [email protected].

A: There are two parts to the answer. First you need a timestamp of some sort and you need to know when seven days ago actually was. When you know both those things, it’s a simple “greater-than” comparison to identify new documents. That opens the way to you using Studio 3T Tasks to regularly run an export or report and using placeholder names for destination files to create unique archives.

Let’s start with the timestamp. If your schema doesn’t have a timestamp field for when a document was created (or modified), you can’t really know the history of any document. Many schemas won’t have timestamp fields because people don’t expect them to be useful. 

So, let us assume you just have your collection documents with their data and the server-generated _id field for indexing. Can we work out when a document was created? 

The Hidden Timestamp

If you’ve let MongoDB generate the _id’s value, there is. The purpose of an ObjectId is to be as unique as possible. To that end it includes a 5 byte random value and a 3 byte counter, and a 4 byte timestamp. You can read about that in the MongoDB ObjectId documentation. All that uniqueness is created when you insert a document for the very first time.

It’s the timestamp in the _id field that we can use to find the last seven days of new documents. The question is, how do we access that timestamp information. ObjectId has a .getTimestamp() method in JavaScript and in Mongo shell, but that’s not something we can use when constructing a MongoDB query. 

To get that timestamp we have to look at the aggregation function $toDate. While it would most likely come to mind for converting strings and numbers to Date values, it has a trick up its sleeve. When given an ObjectId, it returns the timestamp from that ObjectId as a Date. That looks like this in practice:

{ $toDate: "$_id" }

Now, the timestamp’s date value is rounded to seconds, but when we use it, it will return a value in milliseconds, like other dates in MongoDB.

How Soon Is $$NOW?

The next bit of information we need is when was seven days ago from right now. MongoDB’s aggregation system variables give us the “right now” through the $$NOW variable that returns the current date value. 

Aren’t there other ways of getting the current date and time? Yes, but they always give the very latest current date and time. If your aggregation takes time, that means that value drifts. With $$NOW, it’s locked to one time value for the time it takes to run an aggregation which makes it much more consistent.

To make $$NOW into seven days ago, we need to work out the number of milliseconds that equals seven days and subtract that from $$NOW. Well, one day has 86,400,000 milliseconds (1000*60*60*24) so we’ll use that as our starting point and just multiply it by 7 to get seven days:

{ $multiply: [ 86400000, 7 ] }

You can bookmark this simple expression for future use. Just change the second parameter to set the number of days you need and avoid messing about with the calculator. You can also immediately see the number of days without having to divide by 86400000. We have to subtract that from $$NOW:

{ $subtract: [ "$$NOW", { $multiply: [ 86400000, 7 ] } ]  }

We now have a way to calculate, within an aggregation, seven days ago.

Putting the Timestamp and $$NOW Together

We want to wrap both these parts, the _id timestamp and the seven days ago calculation in a greater-than expression. If the timestamp is greater than seven days ago, it was created in the last seven days.

$gt: [ { $toDate: "$_id" }, { $subtract: [ "$$NOW", { $multiply: [ 86400000, 7 ] } ]  } ]

We’re almost there. To use this in an aggregation’s $match stage we’ll need to use $expr to have it evaluated.

Create an aggregation $match stage and drop that in. The pipeline will now only contain records newly created in the last seven days.

{
    $expr: {
        $gt: [
            {
                $toDate: "$_id"
            },
            {
                $subtract: [ "$$NOW" , { $multiply: [86400000, 7 ] }]
            }
        ]
    }
}

Once you’ve fine tuned your Aggregation query, click on Studio 3T’s export tool to turn the aggregation into a source for an Export. Select  “Current Query Result” as the export source when asked

Current query results is just one of the sources you can use for exports

Save the Export as a Task to be able to repeatedly run it and use Studio 3T’s Task scheduler to regularly run the export. If you are exporting to files, don’t forget to use placeholder names for destination files to create unique filenames.

Express It As A Find Query

We’re not done yet. Using $expr to wrap it also means we can use that same query in a find. The query would look like this in Studio 3T:

Using out $$Now and the _id timestamp in a find() query
Using the Aggregation’s expression in a Find query

It’s a superpower worth knowing about that $expr lets you tap into the rich range of aggregation operators from inside traditional MongoDB queries.

Caveat Queryor

Remember that the ObjectId is only time stamped once on creation where it didn’t exist before. If you are copying collections and copy the _id field over to the new collection, you’ll find everything retains the time it was initially created with. If you copy over without the _id field, a new _id will be created but the collection will have a new index too.

Using the _id timestamp is a useful method of identifying new documents, but if you want to also capture modified documents you’ll need your own time-stamping. We’ll talk about that in a future AMA on this subject.


How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

DJ Walker-Morgan

Dj has been around since Commodore had Pets and Apples grew everywhere. With a background in Unix and development, he's been around the technology business writing code or writing content ever since.

Related articles

  • Regularly Exporting MongoDB Data #Studio3T_AMA
  • Finding your MongoDB version
  • MongoDB, A Database with Porpoise – When You Have a Whale of a Performance Problem
  • AMA: Do you have any shortcuts to Studio 3T productivity?
  • I don’t get unwind in aggregation – why do I need it?

Tags

academy aggregation AMA atlas community connections date tags events export features hackolade import intellishell JSON knowledge base migration modelling mongodb mongoodb productivity query regex releases schema security SQL Studio 3T tasks tips updates webinar windows

Browse by MongoDB topic

  • Connecting to MongoDB
  • Database Administration & Security
  • Getting Started with MongoDB
  • Getting Started with Studio 3T
  • Import/Export
  • Job Automation & Scheduling
  • MongoDB Aggregation Framework
  • MongoDB/Studio 3T Workshops
  • Performance
  • Query & CRUD Operations
  • Reference
  • Schema
  • Studio 3T Licensing
  • Support and other resources
  • Working with MongoDB & SQL
  • Working with MongoDB Atlas

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • Case Studies
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2022 3T Software Labs GmbH. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum
When you click "Accept", you are agreeing to cookies being on your device. They may improve site navigation, site usage analysis, or the relevance of messages. It is up to you which cookies are enabled. Read our Privacy Policy.
Manage cookies
Accept
✕

Privacy Preference Center

A cookie is a small file of letters and numbers that is downloaded on to your computer when you visit a website. Cookies are used by many websites and can do a number of things, eg remembering your preferences, recording what you have put in your shopping basket, and counting the number of people looking at a website. In the form below you can choose which cookies, except for essential cookies, to allow or disable.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected