Question

Extract (only) hour from Date

  • 3 August 2020
  • 3 replies
  • 172 views

Userlevel 1
Badge +1
  • Frequent Contributor
  • 6 replies

How would I use regex/custom fields to extract only the hour portion of a date value?

So for example if I want to group by the 9am, 10am hours rather than Mixpanel’s default behavior with the ‘hour’ selector of grouping by hour AND day, how would I do this?

Thanks in advance

 


3 replies

Userlevel 3
Badge +1

Would the following work for you? See example here: https://mixpanel.com/s/1FG6AZ

 

 

And let’s say you wanted to exclude certain hours from the report, you can just click on any of the bar to exclude them:

 

Hope that helps!

Userlevel 1
Badge +1

@Peishan  is ‘hour of day’ new? Yes that should work! Now, how would one use just the hour portion in custom properties (e.g. to create custom ranges based on a combination of date.hour, and ‘Timezone’ (America/New York)? 

 

Essentially I’d like to breakdown certain events by the quarters of the day our users execute them e.g.

4am-10am “Morning”

10am-4pm “Mid Day”

4pm-10pm “Evening”

10pm-4am “Late Night"


But to do this, I need to set ranges based on the Date.hour and some adjustments based on project time (Mountain time) and the timezone of the user.

Userlevel 3
Badge +1

Hi Adam,

 

To extract the hour in custom properties, you can use the formula REGEX_EXTRACT.

Try something like this: REGEX_EXTRACT(string(DATE), “([0-9]+):[0-9]+:[0-9]+“,1) to get your hours, then you can create IFs conditions to group your hours together.

More details about custom properties here: https://help.mixpanel.com/hc/en-us/articles/360030848432

Reply


Mixpanel