Solved

Timestamp event properties in UTC

  • 11 June 2020
  • 2 replies
  • 378 views
J
Badge

We’re looking to add a new event property “UTC Event Time” that’s accurate to the millisecond to help us sort events into their proper order.  The values that we’re sending for this property aren’t what we see on the server, however.  Instead of UTC the values seem to be shifted to local time.

 

Format: "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"   

We send: 2020-06-02T11:04:21.548Z      -> We see: 2020-06-02T04:04:21.548

 

Any advice here?  We’ve changed the format into something not directly castable to SQL DateTime to get around this, but that is making our downstream processing much more difficult.

 

icon

Best answer by jcoplen 26 August 2020, 23:33

@cherise 

Hey, thanks for getting back to me.  We were sending the timestamp as a separate event property “UTC Event Time” as a formatted string that Mixpanel was modifying.  We found that if we send it as a 64-bit integer Unix timestamp, it’s not modified, but still easy to cast to a SQL DateTime value. This is the solution we went with.

Note, even after setting our projects time zone to UTC, we still seemed to get unpredictable results.  It’s possible that we were looking at “old” events that had already been modified, though.

Take care,
-Joe

View original

2 replies

cherise
Userlevel 6
Badge +3

@jcoplen —

 

Have you seen this article discussing timestamp? 

 

Mixpanel records all events in Coordinated Universal Time (UTC) at intake, and converts the timezone to your project timezone before writing the event to Mixpanel data stores. For example, if a project is set to Pacific time (UTC -8), Mixpanel subtracts eight hours from the timestamp before recording it in the database.

If you change your project timezone (and if you want to do this, the directions are within the linked article) it only changes the conversion applied to new events. That said, it is **IMPORTANT TO NOTE** that if you change your timezone, old events remain in your project’s previous timezone because events are immutable–once they are recorded in Mixpanel, they cannot be changed.

 

It sounds like the issue here is the timezone, but if the issue is the format, I would suggest for you to record a separate property that is not $time that is in the format that you need to work with. That way, Mixpanel can use the reserved $time to be compatible with our systems, and you could have a separate property compatible with yours.

 

I hope this helps!

J
Badge

@cherise 

Hey, thanks for getting back to me.  We were sending the timestamp as a separate event property “UTC Event Time” as a formatted string that Mixpanel was modifying.  We found that if we send it as a 64-bit integer Unix timestamp, it’s not modified, but still easy to cast to a SQL DateTime value. This is the solution we went with.

Note, even after setting our projects time zone to UTC, we still seemed to get unpredictable results.  It’s possible that we were looking at “old” events that had already been modified, though.

Take care,
-Joe

Reply


Get inspired by Mixpanel Moments:

View webinar View webinar
Mixpanel