Skip to main content

Is there any way to reference the time of an event in a derived property formula?

 

I am trying to create a derived property that calculates the difference in time between a date in a user property (the users account creation date) and the date of the event being measured. I have gotten it to work using the TODAY() function, but I can’t seem to find any way to reference the event time itself. TODAY() only works if looking at the last N days, but I can’t use it to create historical trends. 

Thanks

DIVIDE(
	MINUS(
		TODAY()
        ,
        DATE_TO_LONG(
			REGEXEXTRACT(
				PROPERTY(
					'first_insert_date',
					'user'
				),
				"^\d{4}-\d{2}-\d{2}"
			)
		)
	),
	86400000
)

 

Hi Haytham,

Thank you for writing in. Happy to help!

Currently, the event timestamp is not directly available for use in derived properties. However, there are a couple of workarounds you can consider.

1. Use the `server_upload_time` property: This property represents the time when the event data is received by Amplitude's servers. If your events are being ingested in real-time, the `server_upload_time` and `event_time` would be very close to each other. Here's an example of how you can use the `server_upload_time` property:

DIVIDE(
	MINUS(
		PROPERTY('server_upload_time','amplitude_user')
        ,
        DATE_TO_LONG(
			REGEXEXTRACT(
				PROPERTY(
					'first_insert_date',
					'user'
				),
				"^\d{4}-\d{2}-\d{2}"
			)
		)
	),
	86400000
)

2. Create a custom property to track the event time: If you need to use the exact `event_time` in your calculations, one workaround could be to instrument a custom event property to track the event time on the client side, and then use this custom property in your derived property definition.

I hope this helps. Please don't hesitate to let me know if you have any questions.

Best regards,
Thao


P.S. Checkout upcoming events and user meetups on our events page.

Thank you, the server_upload_time isn’t exactly accurate for me but is close enough, I’ll use this approach!

 

It would be nice to get event_time added to the available properties to reference without additional instrumentation. Feels like it should be possible if server_upload_time is?

Hi Haytham,

You are welcome! I understand that using the `server_upload_time` might not be the ideal solution, but I'm glad to hear that it's close enough for your current requirements.

If you have any other questions, please do not hesitate to contact us. We will gladly address them!

Best regards,
Thao


P.S. Checkout upcoming events and user meetups on our events page.

Reply


Terms & ConditionsCookie settings