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

Date Tags: Getting It Right On DateTime

Posted on: 11/05/2022 (last updated: 12/05/2022) by DJ Walker-Morgan

Dates and times are tricky things to work with, mostly because we live, quite literally, in the “now”, and measure time relative to that “now”. Converting “now” into something a database query can work with is hard enough. Converting the idea of the “last week” is harder still. Which is why we’ve added Date Tags to Studio 3T, to make life with dates and times easier.

For example, with MongoDB, you’d have to use the MongoDB Date() function and calculate – if you wanted to reuse the query in the future – the offsets needed to point to the right time in the past or future. And that would be for just one end of a date range – you have to do the same calculation again for the other end of the range. Then wrap it up in a query expression with a greater than/less than, as appropriate. Oh, and calendar aware calculations are even trickier to perform as you have to “round” to the nearest year, month or day. Most people literally don’t have time for that.

Introducing Date Tags

Date Tags take all that hassle away and allow you to quickly specify a time range in a human friendly format. New in Studio 3T 2022.4, they are short statements about time, preceded by a # symbol. When a query is run, the date tag is converted into a query which resolves precisely to your requested time range. 

There are two kinds of date tags: relative and absolute.

  • Relative date tags are based on “now” and cover a range of times based on “now” – things like today, last week, last year, and next 5 years. 
  • Absolute date tags are specific points in the calendar and cover a particular range – before or after a particular year/month/day, or between two particular dates. They don’t change over time.

Relative date tags use the time of query execution as “now”. This is used to create a greater than (or equal) and less than (or equal) comparison. That calculated comparison can then be  applied to date fields. 

Date tags can currently be used in Studio 3T’s Collections View, Aggregation Editor, and in Export queries. Date tags only work with MongoDB date type fields, so be sure that your data is correctly typed. You won’t get far trying to query a string which looks like a date with a Date tag.

Date Tags – How Do They Work

Let’s look at the #today Date Tag to see how that works. If we create a query that includes:

{ registered_on: #today } 

When the query is run, and given I’m writing this on the 27th of April 2022, it will be converted into a query like this:

db.getCollection("customers").find(
    { 
        "registered_on" : { 
            "$gte" : ISODate("2022-04-27T00:00:00.000+0000"), 
            "$lt" : ISODate("2022-04-28T00:00:00.000+0000")
        }
    }
);

You can use the Query Code feature of Studio 3T to see this transformation yourself. What we see here is that the generated comparison looks for any time from (and including) midnight today, all the way to any time before (and not including) midnight tomorrow. That makes for a definitive day – from midnight to midnight. Run the query tomorrow, and the dates will be adjusted to tomorrow. 

The Day, Today

That’s just for #today. There’s #yesterday and #tomorrow too if you want to ask about days. If you just want to know about the future or past, there’s #uptonow and #fromnow which covers any time (including today) which is before or after now. 

“Now” is of course a particular time of the day, so if you ask for #uptonow, you’ll get results which include everything including the early hours and morning if you query in the afternoon. If you are actually more interested in things that happened before or after today – which started at midnight and ends at midnight – there’s #beforetoday and #aftertoday. This is what we call a calendar-based date tag, it uses whole calendar units for the calculations. In this case, whole days.

Now, date tags would be quite limited if they only dealt with one day as a range. That’s why we have tags like #nextNdays and #lastNdays. You replace N with a number when you use these tags, so #next5days are the next 5 days, from now and #last6days are the previous 6 days from now. There’s also calendar-based versions #nextNcaldays and #lastNcaldays which exclude today.

Date Tags and Going Weekly

Days make weeks and the #next7days makes up #nextweek. That, again, is the next 7 days from right now. If you want the next 7 whole days, excluding today, you could use #next7caldays and similarly, if you wanted the next week after this current week, #nextcalweek would be what you are looking for. If you wanted a number of weeks in the future, just add a value to get #nextNweeks and #nextNcalweeks.

You can do the same with #lastweek, #lastNcaldays, #lastcalweek, #lastNweeks and #lastNcalweeks going back in time. 

Going Monthly and Yearly with Date Tags

Now you should be seeing a logic in this arrangement. The same pattern as for days and weeks also works for months and years. Just use months and calmonths or years and calyears. #thismonth, #nextmonth, #nextcalmonth, #lastmonth, #lastcalmonth, #thisyear, #nextyear, #nextcalyear, #lastyear and #lastcalyear also all behave as you expect. Where “cal” is included, the calculated dates are based on whole calendar units again.

Absolutism

Not everything is about now. #1974 can refer to that whole calendar year, #1974-05 can refer to the whole calendar month of May in 1974 and #1974-05-13 can refer to the day that was 13 May 1974 – so that’s #yyyy, #yyyy-MM and #yyyy-MM-dd. Once you are referring to particular days you can use #beforeyyyy-MM-dd or #afteryyyy-MM-dd to find documents with times before or after a particular date. Finally, there’s #yyyy-MM-dd_to_yyyy-MM-dd which lets you find any MongoDB date between (and including) the two specified dates.

Finally – Hours

There’s one unit of time we haven’t mentioned. Hours are also usable in date tags. The options here include #nexthour, #lasthour, #nextNhours and #lastNhours. All of them work with the exact time in the hour and each hour is precisely 60 minutes.

In Closing

And that’s a tour of the current set of date tags in Studio 3T. You can find the full reference in the Studio 3T Knowledge Base under Date Tags. They make date comparisons with the current time much much easier and should help everyone compose more fluent queries that are right on time, every time.


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

  • Getting Data Masking and Anonymization Right
  • Date Tags
  • What’s New in Studio 3T 2022.4 – Date Tags, Smarter Columns, Saving SQL Mappings
  • Getting Started with MongoDB – An Introduction
  • MongoDB Atlas, Read Preference Tags and Studio 3T

Tags

academy aggregation AMA atlas community connections date tags 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
  • 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