Extracting Hour from Time Event Property with Custom Formula
write a custom property formula to extract hour of the day from Time event property
write a custom property formula to extract hour of the day from Time event property
Hi Akshay J., Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
To extract the hour of the day from the Time event property in Mixpanel using a custom property formula, you can use the following approach:
``` plaintext floor((Time % 86400000) / 3600000)
LBKS- `Time` is the event property representing the timestamp in milliseconds.
- `86400000` is the number of milliseconds in a day.
- `3600000` is the number of milliseconds in an hour.
- The formula calculates the milliseconds elapsed since midnight (`Time % 86400000`), then divides by the number of milliseconds in an hour, and finally uses `floor()` to get the hour as an integer.
This formula leverages the available numeric operators and the `floor()` function for custom properties in Mixpanel. You can enter this formula in the custom property builder as described in the documentation. If you need to reference the property, use the property selector in the formula editor to ensure the correct property name is used [Custom Properties: Calculate new properties on the fly](https://docs.mixpanel.com/docs/features/custom-properties#functions).
If your Time property is not in milliseconds, you may need to adjust the formula accordingly.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
we dont have the above functions like to_datetime or .hour rewrite the formula using customn property in mixpanel write a verified and validated formula to convert above time into hour of the day (0 to 23) "time": 1752485760.999 "$mp_api_timestamp_ms": 1752485765131
Let me check the knowledge base for you again. One moment! 📚
To extract the hour of the day (0 to 23) from the Mixpanel Time event property using a custom property formula, you need to consider the data type and format of the Time property:
- The Time property in Mixpanel is a Unix timestamp, typically in seconds (e.g., 1752485760.999), not milliseconds.
- To get the hour of the day, you must:
1. Convert the timestamp to milliseconds (if needed).
2. Calculate the number of hours since midnight UTC.
Based on the available Mixpanel custom property functions and operators, you can use the following formula:
``` plaintext floor((Time % 86400) / 3600)
LBKS**Explanation:**
- `Time % 86400` gives you the number of seconds since midnight UTC (since there are 86,400 seconds in a day).
- Dividing by `3600` (seconds in an hour) gives you the hour of the day.
- `floor()` ensures you get an integer value from 0 to 23.
**How to use:**
- Enter this formula in the custom property builder for your report.
- Make sure you are referencing the correct property name (`Time`).
**Reference:**
This approach is based on the supported numeric operators and the `floor()` function for custom properties in Mixpanel, as described in the documentation [Custom Properties: Functions](https://docs.mixpanel.com/docs/features/custom-properties#functions) and [Supported Data Types](https://docs.mixpanel.com/docs/data-structure/property-reference/data-type#overview).
If your timestamp is in milliseconds (like `$mp_api_timestamp_ms`), you would need to divide by 1000 first, but for the standard `Time` property in seconds, the formula above is correct.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Let me check the knowledge base for you again. One moment! 📚
To convert the hour derived from the Time property to IST (Indian Standard Time, UTC+5:30) in a Mixpanel custom property formula, you need to adjust the UTC hour by adding 5.5 hours. However, Mixpanel’s custom property formula language does not support fractional hours directly (i.e., you cannot add 5.5, only whole numbers).
A practical workaround is to add 5 hours, then add 1 more hour if the minutes are 30 or more. However, based on the available functions in Mixpanel custom properties, there is no direct way to extract the minutes from the timestamp or perform modulo operations with non-integer values.
Given these limitations, you can approximate IST by adding 5 hours and, if possible, handling the 30-minute offset as best as the formula language allows. Here’s how you can do it for the hour (not accounting for the 30-minute offset, due to formula limitations):
``` plaintext mod(floor((Time % 86400) / 3600) + 5, 24)
LBKS- `floor((Time % 86400) / 3600)` gets the UTC hour.
- `+ 5` shifts the hour to UTC+5.
- `mod(..., 24)` wraps around if the hour exceeds 23.
**Note:** This will give you the hour in IST rounded down to the nearest hour, but it will not account for the 30-minute offset. If you need exact IST hour (including the 30-minute offset), Mixpanel’s custom property formula language does not currently support this level of precision, as there is no way to extract minutes or handle fractional hours in the formula language.
If you need exact IST time (including the 30-minute offset), you may need to handle this transformation outside of Mixpanel or request this feature from Mixpanel support.
**References:**
- [Custom Properties: Functions](https://docs.mixpanel.com/docs/features/custom-properties#functions)
- [Supported Data Types](https://docs.mixpanel.com/docs/data-structure/property-reference/data-type#overview)
I'm sorry, but due to the limitations of Mixpanel's custom property formula language, you cannot fully account for the 30-minute offset for IST within a custom property formula.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.