Reference event timestamp in a derived property?

Haytham

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(<br />	MINUS(<br />		TODAY()<br />        ,<br />        DATE_TO_LONG(<br />			REGEXEXTRACT(<br />				PROPERTY(<br />					'first_insert_date',<br />					'user'<br />				),<br />				"^\d{4}-\d{2}-\d{2}"<br />			)<br />		)<br />	),<br />	86400000<br />)

 

Thao Nguyen

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(<br />	MINUS(<br />		PROPERTY('server_upload_time','amplitude_user')<br />        ,<br />        DATE_TO_LONG(<br />			REGEXEXTRACT(<br />				PROPERTY(<br />					'first_insert_date',<br />					'user'<br />				),<br />				"^\d{4}-\d{2}-\d{2}"<br />			)<br />		)<br />	),<br />	86400000<br />)

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.