Date and Time are ubiquitous in Planhat, as in life, but it is easy to go wrong! This article explains how Planhat works with Date and Time, and provides some guidelines on how to use different fields.

Planhat supports 3 field types for storing Date/Time:

Many of Planhat's system fields are of type DateTime. For example, the date of an email, or the date that a company was created in Planhat, is stored with an exact timestamp. Other system fields, like License start date, or Invoice due date, are of type Date.

When you create custom fields, you can choose whether it should be a Date, or a DateTime. In most cases you may find that Date is not only sufficient, but preferable.

We recommend only using DateTime fields when there is a noticeable benefit to tracking the exact time. For example, you may be importing data on usage, where you care about the exact time something happened.

There are 4 factors determining how Date and Time are displayed:

Your personal timezone preference determines the timezone in which you both see, and set, DateTimes in Planhat. For example, if your timezone preference is GMT+3 and you are viewing the DateTime: July 26, 2023 15:00 GMT, this will be displayed back to you as July 26, 2023 12:00 GMT+3. The same logic applies when you set the DateTime.

Planhat's timezone settings also take account of Daylight Savings. For example, if you are based in Stockholm, Sweden, you spend half of the year in CET (GMT+1), and the other half in CEST (GMT+2). If your timezone preference is "Stockholm", and you set a DateTime for December 17, 2023 21:00, this will be received by Planhat as December 17, 2023 21:00 (GMT+1), because that is the GMT offset that is applied in Stockholm, in December.

Setting Tenant Date and Time Preferences

Setting User Date and Time Preferences

Planhat stores Date and Time in ISO format, in GMT. When you view DateTime from a non-GMT timezone, we are converting the DateTime that we store, into your timezone. We do the reverse when you are setting DateTime from a non-GMT timezone.

Our Day fields store Dates in epoch format. We generally recommend using Date fields instead of Day, as these are more compatible with third party integrations, as well as our own internal calculations using Formulas and Automations.

This is explained at depth in this article.

There are two ways of switching between field types, after a custom field has already been created:

When you switch a DateTime field to be of type Date, this means that we remove the timestamp from the data on the field. You can choose to push the Date "backward" or "forward", when saving the field. This means that your DateTimes are either pushed forward to the next GMT day, or backward to the same GMT day. For example, the DateTime of December 31, 2023 15:00 (GMT) can either be pushed backward to December 31, 2023 00:00 (GMT), or forward to January 1, 2024 00:00 (GMT).

📌 Please note that Planhat pushes backward or forward based on the GMT DateTime. For example, if you are in Los Angeles and have a DateTime: July 4th, 23:00 (GMT-7), this is July 5th, 06:00 (GMT). If you push "backward", you will get July 5th, and if you push "forward" you will get July 6th.

When you switch a Date to be of type DateTime, this just means you can start adding a timestamp to Dates on this field. You will also be forced to specify a DateTime when using filters and other condition builders. For example, if you want to see "all endusers created after December 20, 2024", you will need to specify a time as well, since our created dates always come with a timestamp.

It is worth noting that this migration is also applied to any filter rules, automations, or other "condition builders". If you switch a DateTime field to Date, and push backward, then any rules referencing this field are also changed to to "Date" and pushed backward! However, when switching field type, please be sure to adjust your integration settings, as the field type specified in e.g. the Hubspot and Pipedrive integrations, is not updated when you make the switch. You have to do it manually.

Switching Field type per Field DateTime --> Date

Switching Field type per Field Date --> DateTime

Switching Field type in bulk, Using the Powertool

Condition Builders

When you use an "absolute" rule, such as "After (date)", while referencing a DateTime in a condition builder rule (e.g. filters, field conditions, automations), you must specify a time component in addition to the date component. For Date fields, this is not the case.

For "relative" conditions, like "After 9 days ago" or "Last week", you will notice that you must specify a timezone, regardless of whether you are referencing a Date or DateTime field. This timezone can be thought of as the filter's, or more specifically the rule's, timezone perspective. In Los Angeles, "Last week" becomes "This week" several hours later than in London, because Los Angeles is 7-8 hours behind GMT. So we let you specify what timezone your filter "thinks" in. In most cases this will not matter to you. The timezone defaults to the condition creator's timezone preference, on creation. But if you have a number of automations, email campaigns, and other processes that are triggered by filter movements, you may be sensitive to the time that our filters change their "perspectives".

More generally, it is worth noting that many of Planhat's condition builders are universal. A filter, for example, contains the exact same records, whoever is looking at them. Some may be hidden due to permissions, and the DateTimes may display differently based on your timezone, but the same records are contained in the filter. This is important, because filters are core inputs into automations, dashboards, workflows, and other processes - so they can not be flexible to the user viewing them at a point in time.

For Tasks, when referencing the Due Date of a task, we apply a particular logic. This is because Tasks can be of type "All day", or they can have a specific time. This means we need to manage rules like "Before (date)" and "After (date)" in a unique way. This is outlined in more detail in the Actions Module section, below.

Formula Fields

When referencing Date and DateTime in Formula Fields, it is worth noting that our DAYS_DIFF and other operations "think" in 24 hour periods. so January 2 - January 1 = 1, January 2 12:00 - January 1 13:00 = 0, et.c.

When filtering Tasks on Due Date, you will notice that condition builders behave slightly differently than described above - specifically, we ask you to specify timezone even when applying "absolute" rules like "Before (date)" and "After (date)". This is because Tasks can be "All day", or they can have a specific time. If you want Tasks with Due Date after July 4th, 21:00, Planhat needs to know what timezone you are applying, in order to know which "All day" tasks should be included. For example, if you want "after July 4th, 21:00, timezone: Los Angeles" then Planhat knows to include "All day" tasks that are due on or after July 4th. Without explicitly specifying the timezone, "after July 4th, 21:00" would just be converted to GMT --> "after July 5th, 04:00", and "All day" Tasks for July 4th would be excluded. When the timezone is specified, you are helping our condition builder define the "day", and match it to "All day" tasks.

The Actions Module can display Tasks in calendar view or list view. These are not universal filters - they adjust to your personal timezone settings.

In calendar view, each column represents a date. Tasks with a specific time, will fall in the date column that corresponds with your personal timezone setting. "All day" tasks (without a specific time), will fall into the same date column for all users - i.e. a Task with no specific time, and the Date value July 26th, will always fall into the column for July 26th, for all users, whereas a Task with a specific time will take account of the user's timezone preference when allocating it to a column.

Similar logic applies to the list view of tasks.

When mapping Date and DateTime fields to third parties, such as Salesforce, Hubspot, Pipedrive, Snowflake, et.c. you will find that the fields are compatible. If you attempt to send a DateTime to a Planhat Date field, we will receive it, but will drop the timestamp (i.e. push backward) by the same logic as explained in the section for switching field types.

If you map a 3rd party "Date" field to a Planhat "DateTime" field, you may see some unexpected DateTime values. This is because Planhat receives a Date field, e.g. "July 4th", as "July 4th, 00:00 AM GMT" (a standard way of saving Dates). Since you mapped it to a DateTime field in Planhat, you will see this DateTime value. Furthermore, you may have a non-GMT personal timezone preference - this means "July 4th" from Hubspot or Salesforce, may show as "July 3rd, 20:00 (GMT-4)" or "July 4th, 04:00 (GMT+4)".

We recommend that you map Planhat Date fields to third party Date fields, and Planhat DateTime fields to third party DateTime fields to avoid any confusion or data corruption. (You can switch the field type in Planhat if you want to keep using a pre-existent field!)

Data Tables, Widget Filters, and non-timeseries Widgets

These behave the same as elsewhere in the app

Trend Charts (Cohort Bar, Cohort Line)

When applying a date aggregation (week, month, year, et.c.), the allocation of records into the different "buckets" (such as weeks), references the tenant timezone. This can't be changed, and can't be dynamic based on the user who is viewing the widget. Otherwise your KPI dashboards might display different data to different viewers!

Timeseries Charts

Planhat's Timeseries data is timezone agnostic. We receive timeseries data, including a timestamp, as if it is in GMT, and we display it back with no reference to timezone. In other words, if you send us an event for the 4th of July, 17:17 PM, it is saved as such, and it is counted and displayed for the 4th of July. The fact that this is saved in GMT is not important in itself, as we do not surface or take account of timezones - it just means that all users see metrics data in the same way, regardless of location.

Portals

A Planhat user who accesses the Portal will have his or her personal timezone preference apply when viewing and updating DateTime values. An enduser, however, will have the browser timezone applied.

License start and end dates are system fields of type date - so they are shown with the same value regardless of timezone.

When date-filtering in the Renewal Rate, Renewals, and Bookings tabs your browser timezone is applied. Please note that this is not the same as your user timezone preference. For example, you might have selected a custom timezone preference for Los Angeles, but when you select "today" or "yesterday" in the Revenue Module, "today" and "yesterday" are defined based on your browser timezone - i.e. where you are physically located.