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.
