Are you making the most of datetime properties? In this blog, we will explore the world of datetime properties, covering essential topics such as practical applications, compatibility with date properties, setting and creating datetime properties, and even backfilling datetime values. Let's dive into this!
What is a Datetime Property?
A datetime property goes beyond simply displaying dates; it also includes time values. To illustrate, let's explore the distinction between a date property and a datetime property:
As you can observe, the datetime property not only displays the time but also includes a "GMT-3" component, indicating the user's specific time zone. This adjustment occurs because the value of every datetime property is tailored to the timezone of each user. Consequently, depending on the user's timezone, what appears as "12:00 AM GMT-3" to one user may appear as "6:00 AM GMT+6" to another. Importantly, this adjustment doesn't alter the underlying property value; rather, it serves to provide each user with a clear understanding of when the time was originally set under their timezone.
When to use Datetime Properties
These are the three main scenarios where we recommend using datetime properties:
👉 When you need to calculate the time between properties:
Some examples are SLAs, time between deal stages, and time between lead statuses.
👉 When you need to add time to certain properties:
If you need to add hours to a date property or any time to a datetime property, you will need a timestamp property to achieve it.
👉 To increase the accuracy of date properties set by workflows:
For enhanced data accuracy, it's advisable to include time information when setting date properties through automations.
Some examples are: “First meeting scheduled timestamp”, “Received marketing email 1 timestamp”, “Replied to sales sequence timestamp”, etc.
On the other hand, if a property relies solely on manual inputs, it's not recommended to convert it into a datetime property. This is because users won't have the option to specify the time, potentially leading to data inaccuracies.
👉 To trigger automation:
In contrast to date properties, timestamp properties can be set to multiple values within the same date which can be used to trigger workflows when the property is known multiple times in the same day.
Utilizing datetime properties in these contexts can enhance data analysis, improve automation, and provide valuable insights into your business or application.
Date and Datetime Properties Compatibility
In the backend, both date and datetime properties are stored as 13-digit UNIX timestamps. In simpler terms, these timestamps signify the number of milliseconds that have elapsed since 00:00:00 UTC on January 1, 1970. However, there's a key distinction: for date properties, which don't incorporate time values, their UNIX timestamp is set to midnight of the respective date.
The shared essence of these properties makes them versatile, allowing for a smooth data transfer between them via workflows. When a datetime property is copied into a date property, only the date is transferred. Conversely, if a date is copied into a datetime property, the time component defaults to midnight.
Setting Datetime Property Values
✔️ Using Workflows: You can employ workflows to establish a datetime property value. This can be achieved by setting a datestamp or by copying the value from another datetime property.
✔️ Utilizing the API: Alternatively, you have the option to utilize the API to set datetime property values programmatically.
✔️ Time Between Calculated Property: You can create a calculated property that outputs the time between two other timestamp properties to generate a new one. Check out this knowledge base article to learn all the details about this type of property.
✔️ Custom Equation: If you are looking to add or subtract hours or days to an existing timestamp property, you can create a calculated property that uses a custom equation in advanced mode but note that all the time added to a timestamp property has to be converted to milliseconds. In the image below, 1 day is added to an existing datetime property:
Finally, be aware that when a user manually assigns a value to a datetime property, it's important to note that the time will be automatically set to 12:00 AM in alignment with the account's timezone.
Drive Powerful Results
There are three different approaches to creating a datetime property, and the most suitable method will depend on your specific use case:
If you want to convert a date property into a datetime property:
- To achieve this, the most efficient method is to update the existing date property's type to datetime.
- This can be accomplished through an API call to the Properties API.
- After updating the property's type to datetime, it's important to note that all date values will automatically default to midnight UTC. Don’t worry, no data will be lost through this process.
- Since the displayed time varies according to each user's timezone, the offset can affect the date displayed as shown in the image below.
It's worth emphasizing that both values in the illustration are identical, however, to avoid creating confusion, we recommend adding 12 hours to all datetime values. You can achieve this through a workflow or by using a local script in conjunction with the APIs.
If you need to create a brand new datetime property, you have two options:
- Clone an Existing Datetime Property
- By far the easiest method to create another datetime property.
- An existing custom datetime property is required. If you don't have one, consider option b.
- Use Properties API
- If you're looking to create your very first custom datetime property, the Properties API is your go-to solution.
Backfilling Datetime Values
If you need to retroactively set the time value component to a date property that has been converted into a datetime property, you can make use of the "value set timestamp" as demonstrated below.
To accomplish this, you will need to use the API to retrieve all the record’s property latest values along with the corresponding timestamps and develop a script or a custom-coded workflow to update the time of each record.
We understand that coding may pose a challenge for some, but worry not! At Process Pro, we've designed a specialized tool to simplify this task for you. If you require any assistance or have questions regarding your datetime properties, we invite you to reach out to us.
Let's chat and explore how we can streamline your data management journey together! Your success is our priority.