Date Tags: Getting It Right On DateTime

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. 

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. They 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. 

You can use Date tags 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.

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.