Date Comparison Not Working

Hi @drewh ! Welcome to the Amplitude Community and thanks for reaching out! 

So it should be the case that you can make date comparisons so long as the format is the ISO 8601 format (YYYY-MM-DDTHH:mm:ss), which will allow you to perform date comparisons in the platform, (e.g. '2016-01-31' > '2016-01-01'). This will also work for datetime values (e.g. '2017-08-07T10:09:08' > '2017-08-07T01:07:00'). Amplitude compares dates as strings. https://developers.amplitude.com/docs/http-api-v2#setting-date-values 

When creating a chart in Amplitude, you should be able to do something like this: https://analytics.amplitude.com/demo/chart/new/2d2k9ac

If you are setting up the chart similarly as I have but the chart is not returning anything, then it is likely that you either are sending the data in the wrong format or perhaps there is no data that is greater than or equal to the date that you input. 

Hope this helps - let me know if you have any questions! 

Hi @belinda.chiu , thanks for the response. I have tried both standard date format (YYYY-MM-DD) and ISO 8601 (YYY-MM-DDTHH:mm:ss) and the same thing happens with both. Now I’m noticing that if I look at each user individually with the user search, all of these new fields appear with the correct values. But if I do any sort of analysis, only one is registering as having a value for those new fields. 

For example, I tested on 10 users. All 10 show the correct details in the user search view, but if I do a user composition chart, it shows that only 1 user has a value for those new fields.

Hi @drewh - thanks for sharing! Given that you have tried the two date formats, I might need to see your charts and user profiles to get a better sense of what is going on. I will ask a Community Moderator to help transfer this post into a Support ticket and continue investigations from there!

Quick update to provide closure to this post for future viewers: 

Drew and I figured out that the reason why the date comparison was not working is because the user properties were sent in after the event and hence not queryable yet on charts. 

Below are the references that I provided in case other people are curious as well! 

To learn how Amplitude applies user properties to events, see https://help.amplitude.com/hc/en-us/articles/115002380567#applying-user-properties-to-events 
To learn how properties are applied as a filter in charts, see
https://help.amplitude.com/hc/en-us/articles/360016257391-FAQ-Why-is-this-user-counted-under-none-

@belinda.chiu To dig further on this topic a bit: for datetime values, must the value strictly adhere to the “YYYY-MM-DDTHH:mm:ss” format? When I view the raw data for events and look at the value for Amplitude-managed fields such as server_upload_time, event_time, processed_time, etc., I see that these fields use the format “YYYY-MM-DD HH:mm:ss.ssssss” (no “T” separating the date and time, and the time has microsecond precision).

I’m just getting set up and this is causing a little confusion for me as to what format I should use.

For some context, I first sent a “create account” event with a createdAt user property with the standard JS ISO string format (“YYYY-MM-DDTHH:mm:ss.sssZ”, millisecond precision with the trailing “Z”). I then tried to see if I could use the advanced user search to filter on the createdAt user property by date (e.g. createdAt < someDate). However, the filter only had two options for the operator: “= (is)” and “∋ (contains)”. This led me to believe that my datetime format was incorrect. I also see in Amplitude Data in my tracking plan that the field has a String type (I expected to see it listed as a Date type, but I guess Amplitude does not have this?).

Then, I used the Identify API to update the createdAt user property to “YYYY-MM-DDTHH:mm:ss” but I observed no change in any of the UIs mentioned above. I then tried changing it to “YYYY-MM-DD HH:mm:ss.ssssss” (the format I observed being used for Amplitude-managed fields), again with no change.

I recognize that it could be because I’m updating the user properties after the event was sent. Anyway, what’s the correct format to use for this datetime field? Is it just the case that if I stick with “YYYY-MM-DDTHH:mm:ss”, things will just start to work?

Thank you!

@Alex Chao thanks for writing in about this and apologies for the delay. I’ve asked the team to look into how we can help you accomplish this on your current plan. If I understand your challenge correctly, what you’re doing is available using Govern, see more details here. 

Thanks for the reply @Esther Trapadoux. Actually, I was just trying to figure out what format I should use in user and event properties for datetime values, if I want to be able to filter and run datetime comparison queries on those properties. I mostly just wanted to confirm that “YYYY-MM-DDTHH:mm:ss” is the correct format.