Understanding Mixpanel Timestamp Timezone Differences in Exported Data vs Dashboard | Mixpanel Community

Mixpanel Community Icon

Understanding Mixpanel Timestamp Timezone Differences in Exported Data vs Dashboard | Mixpanel Community

Vlad S.
·
·
this is also definitely a new change; in the past I've exported events, edited them, and then sent them back to mixpanel, and they were essentially "overwritten" (that's how the $insert_id is supposed to be used); but now, if I export an event, don't even edit it, and send it back to mixpanel, I get a second event, 5 hours off... absolutely atrocious
Julianne Bozzini
APP
·
·
I hate to be the bearer of bad news, but you are correct that older projects need to be shifted by the UTC offset from the project's timezone. For these older projects, Mixpanel converts event timestamps to your project's timezone before storing the event data. This means that the raw event data is stored in your project's timezone, not in UTC. Apologies for not having better news there. Were the past events also exported from the same project? Do you have an example of a response where you didn't see the offset? My understanding is that the behavior seen is expected for older projects and has not changed, but I definitely want to look into it if you saw something else previously. Just to note, Mixpanel's deduplication functionality is not recommended for updating events (it is not intended for upserts). If you reimport the changed data with the same $insert_ids and no deletion, in most cases, Mixpanel will display the most recently ingested event with our deduplication feature, but this is not guaranteed, which can result in some events having the new properties and some showing the older event version.
👍1
Vlad S.
·
·
OK, I think the timestamp issue needs, at the very least, to be explained in much more detail in the documentation. But ultimately, there's a bug in there somewhere. To clarify, in my case with a project pre 2023 and in central US time, if I get a timestamp from the export API is that the number of seconds since the epoch or do I need to add several hours to it to make it so?
Vlad S.
·
·
ultimately I expect a (unix) timestamp to always represent the number of seconds (or ms) since the epoch, anything else is highly misleading and should probably be considered a bug
Julianne Bozzini
APP
·
·
Since your project is pre-2023 in Central US time, you would need to adjust the exported data to have it be since epoch. Depending on what type of language/tool you are using, you could use libraries on the exported data to convert timezones, such as pytz (Python), or if the data is exported to BigQuery, you can utilize the time zone conversions, such as TIMESTAMP(DATETIME(TIMESTAMP_SECONDS(1752580800)), 'US/Central') AS time_utc. Unfortunately, it is the expected behavior for older projects. I understand that this can be misleading, as Unix timestamps are understood to be in UTC. However, this is how timestamps are handled for older projects at this time. I agree that this could be better documented and flagged that to the docs team. Apologies for any confusion caused by the behavior as you looked into the issue.
Vlad S.
·
·
all right, so to summarize, correct me if I got this wrong:
when exporting events (from a pre-2023 project) I need to get the timestamp, convert it to a datetime, drop and replace the timezone of that datetime with my project's timezone (not convert it from UTC to my timezone) and finally convert that to UTC in order to get a proper seconds since epoch timestamp
when importing events, I send the common, proper UTC timestamp
Vlad S.
·
·
also, not to be cheeky, but why can't your export API do this, it's just one line of code (you already know the project creation date and timezone), and it would prevent it from returning false timestamps?
Julianne Bozzini
APP
·
·
Your summary is correct. I flagged the behavior to engineering, but there isn't a timeline or guarantee that the behavior will change.
Julianne Bozzini
APP
·
·
Given that, I'd recommend implementing logic on your side for now to adjust to the timestamp for your use case. Sorry for not having better news there.
👍1
Vlad S.
·
·
no worries, I know how these things go, I work with customers, engineers, cs, product managers, etc. on my end as well, and we run into this kind of stuff in our product as well :D
😂1
Julianne Bozzini
APP
·
·
Appreciate that! Definitely sounds like you know the drill as well.