How can I handle UTC when using TODAY() in custom property?

Hi Eliott C., 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> 👋

To handle UTC when using the TODAY() function in custom properties in Mixpanel, you need to be aware of how Mixpanel handles timezones and date properties. Here's what you need to know: 1. Mixpanel records all events in Coordinated Universal Time (UTC) at intake.2. The TODAY() function in custom properties will return the current date based on your project's timezone setting, not necessarily UTC.3. To ensure consistency, it's recommended to send all date properties to Mixpanel in UTC format.When using TODAY() in a custom property, keep in mind that the result will be in your project's timezone. If you need to work with UTC specifically, you should consider the following: 1. Ensure your project timezone is set correctly in Mixpanel settings. 2. When sending date properties to Mixpanel, use UTC format. 3. If you need to perform date calculations, you can use the datedif function in combination with TODAY().Here's an example of how you might use TODAY() in a custom property to calculate the number of months since a registration date: LBKSdatedif(registration_date, TODAY(), "M")LBKSThis will give you the number of months between the registration date and the current date, based on your project's timezone setting.Remember that changing the project timezone only affects how Mixpanel outputs results, not how the data is stored. For projects created before January 1, 2023, event timestamps are stored based on the project timezone setting at the time of ingestion.For more information on managing timezones and working with date properties in Mixpanel, you can refer to the Managing Projects documentation.Custom Properties provides more details on using functions like TODAY() and datedif() in custom properties.Citation: Managing Projects

ℹ️ 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! 📚

I understand your concern about the formula not producing consistent results for users who joined yesterday, and the values changing at 9AM in your local timezone. This behavior is indeed related to how Mixpanel handles timezones. Let's address this issue and see how we can adjust it to your local timezone.The reason for this behavior is that Mixpanel uses the project timezone setting for calculations involving dates, including the TODAY() function. Here's how you can address this: 1. First, check your project timezone setting: Navigate to your Project Settings and verify the current timezone setting. As mentioned in the Managing Projects documentation, "Mixpanel sets project timezones by city or region. If a city or region observes Daylight Savings Time, the ingestion offset adjusts appropriately."2. Adjust the project timezone: If the current setting doesn't match your local timezone, you can change it. Here's how: - In Project Settings, select your project. - In Overview, find the Timezone field and click the pencil icon. - Select your local timezone from the list. - Click Save. Note that only project owners and admins can change project settings.3. Be aware of data storage: For projects created before January 1, 2023, changing the timezone will not affect previously stored data, as event timestamps were stored based on the project timezone setting at the time of ingestion.4. Consistency in date properties: When sending date properties to Mixpanel, always use UTC format. This ensures consistency across your data. You can then rely on Mixpanel to convert these to your project timezone for calculations and display.5. Adjust your formula: If you're using a formula similar to the example datedif(registration_date, TODAY(), "M"), make sure that registration_date is consistently formatted and stored in UTC. The TODAY() function will then use your project's timezone setting for the calculation.By following these steps, you should be able to align the calculations with your local timezone and achieve consistent results for users who joined yesterday.Remember, changing the project timezone only affects how Mixpanel outputs results, not how the data is stored. If you continue to see inconsistencies after making these adjustments, you may need to review how the registration dates are being recorded and ensure they are consistently using UTC at the point of data ingestion.For further assistance or if you encounter any issues while making these changes, I recommend reaching out to Mixpanel's support team as they can provide more specific guidance based on your project's configuration.Citation: Managing Projects

ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.