Searching for Datetime Values

Dates are an important part of managing user profiles. For example, you’ll often want to know the date that a profile was created. You’ll want to know the date when a user first logged on, or maybe the date when the user last user logged on. You’ll want to know when (if) an account was deactivated; you’ll want to – well, again, you get the idea. Fortunately, the Janrain Console makes it easy to search for user profiles based on the date when something (a log on, a profile update, a birthday) took place.

In fact, the only potentially-confusing thing when it comes to searching for dates is the fact that the Console employs two different date-related attributes: date attributes and datetime attributes. A datetime attribute is an attribute that contains both a date and a time; for example, October 18, 2017, 11:35 AM. By comparison, a date attribute contains only the date: October 18, 2017. The Janrain user schema includes attributes of both types, and while there’s no real difference between the two (at least when it comes to searching for user profiles), it’s useful to understand exactly what you’re dealing with.

For starters, the Console formats datetime values by using ISO (International Organization for Standardization) 8061. If you run a search that returns a datetime value (such as the created attribute), the Console returns values similar to this:

How do you interpret values like those? Here’s how:

Value

Notes

2017

Year. Years must be specified using 4 digits.

10

Month. The leading zero is optional. For example, you can specify September as 09 or as 9.

20

Day. Similar to the month, the leading zero is optional.

17

Hour. Hours are displayed using a 24-hour clock: hour 17 refers to 5:00 PM, and hour 5 refers to 5:00 AM. In addition, displayed times represent UTC (Coordinated Universal Time) time instead of local time. For example, in the 24-hour format, 15:39 converts to 3:39 PM UTC time. If a user logged on at 15:39 UTC time, that means that the user logged on at 3:39 PM in Greenwich, England. Meanwhile, Portland, OR is 7 hours behind UTC time: to determine the local time when an action took place, a Portland area admin must subtract 7 hours from the time displayed in the Console. In this case, 15:39 – 7 hours equals 8:39, meaning that the local time for the logon was 8:39 AM Portland time.

31

Minute. Leading zeros are not optional when it comes to minutes: that’s because 14:02 and 14:2 (which would be interpreted as 14:20) are not the same.

24.401766

Second. Leading zeroes are also required when dealing with seconds.

Is that complicated? Maybe. But, fortunately, you don’t have to worry about the level of precision prescribed by ISO 8061. Instead, when you conduct a search in the Console you only have to deal with the date itself: the month, the day, and the year. In addition, you don’t have to use the ISO 8061 format when searching for those dates. Admittedly, ISO 8061 is still allowed; for example, if you want to find all the user accounts created on September 25, 2017, you create a query like this one (which uses the Year-Month-Day) format):

created = 2017-09-25

But what if you don’t like the ISO way of formatting dates? That’s fine; just us the standard US date format (Day/Month/Year) instead:

created = 9/25/2017

Either way, you get back all the user accounts created on September 25, 2017:

That’s how you search for datetime attributes. So how do you search for date attributes? You use the exact same approach. Because they don’t include the time of day, there’s no doubt that date attributes look different in the Console search results:

Be that as it may, you still search for birthdays (or other date attributes) using the same method employed when searching for datetime attributes:

birthday = 12/24/1963

Could it get easier? Well, maybe. But we’re not sure how.

Good question: what about date ranges? For example, can we search for all the accounts created in the year 2017 or earlier? Or what about all the accounts created in February 2018? Can we do more than just find accounts created on a specific day?

Of course we can; we just have to specify the correct operator and the correct starting/ending point. For example, suppose we do want to get back all the accounts created in the year 2017 or earlier. That means that we want to get back all the accounts that were created before January 1, 2018. In turn, that means we want to use the less than operator (<) to return all the user profiles created prior to January 1, 2018:

created < 1/1/2018

Voila:

To find all the user accounts created in the year 2018 or later, all we need to do is switch to the greater than operator and change our starting date to December 31, 2017:

created > 12/31/2017

That returns creation dates like these:

And, yes, we could have used the greater than or equal to operator (>=) and a start date of January 1, 2018.:

created >= 1/1/2018

That returns all the user profiles created sometime in the year 2018:

Again, you have some flexibility here to search for dates in the way that seems the easiest and most intuitive to you.

To find all the accounts created in a specific month, use a query similar to this:

created >= 2/1/2018 AND created <= 2/28/2018

In the preceding query we’re looking for all the profiles where the creation date is greater than or equal to 2/1/2018 and where the creation date is less than or equal to 2/28/2018. In other words, we’re looking all the profiles created in February 2018:

Keep in mind that, for the moment anyway, you’re limited to searching for dates only: although datetime attributes also keep track of the time of day when an event took place, you can’t do something like, say, search for all the user profiles created between 8:00 AM and 9:00 AM on a specified day. Granted, you can include the time of day in your query; for example, you can write a query similar to this:

created = "2017-10-02 04:16:29.017481"

When you actually run that query, however, the Console strips off the time information, leaving you with this:

created = "2017-10-02"

In turn, you get back all the profiles created on October 2, 2017, regardless of what time of day those profiles were created: